Postgresql: Update Records from a Function Calls from dblink(): Syntax Error

Question

I have a precalc_isochrones_5_miles field in a joa_clinic table which needs to be updated based on the return from another database's drivetime_isochrones_IRFANfunction. I am able to establish cross-db connection using dblink and I am using the following in a loop to pass on four arguments to the drivetime_isochrones_IRFAN function. The function would return the_geom_isochrone geometry value.

do
$$
declare
    f record;
begin
    for f in SELECT clinic_long, clinic_lat FROM joa_clinics
    loop    
    UPDATE joa_clinics SET precalc_isochrones_5_miles = the_geom_isochrone FROM
     SELECT the_geom_isochrone FROM dblink('myconn', format('SELECT  the_geom_isochrone FROM  drivetime_isochrones_IRFAN(%L, %L, %L, %L, %L)', clinic_long,clinic_lat,8046,false) )       
    end loop;
end;
$$

But when I run this code, I get a syntax error:

SELECT the_geom_isochrone FROM dblink('myconn', format('SE...

How to fix it? Note: This is Postgresql 11 running in Windows and I am using pgAdmin.

Thank you!

jjanes · Accepted Answer · 2021-06-25 00:29:56Z

1

The immediate syntax error is that you need parenthesis around your select.... if you want to plug it directly into a FROM. But that still doesn't make much sense. You would then be trying to run an update which updates every row of joa_clinics (because there is no WHERE clause to restrict it), and running that update of every row once for every row.

I think you want just a single update, no loop at all (and thus no DO), and put the dblink subquery in the RHS of the assignment, not in a FROM.

UPDATE joa_clinics SET precalc_isochrones_5_miles = 
   (SELECT the_geom_isochrone FROM 
      dblink('mylink', 
        format('SELECT drivetime_isochrones_IRFAN(%L, %L, %L, %L, %L)', clinic_long,clinic_lat,8046,false,false)
      ) f(the_geom_isochrone geometry)
   );

Now if drivetime_isochrones_IRFAN is a set-returning function, then maybe you want something different than this, but without seeing the definition there is no way to know what that might be.

Note that I added a dummy argument to your function call, because you have 5 placeholders in your format string so need 5 additional arguments to it, not 4.

answered Jun 25, 2021 at 0:29

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

IrfanClemson Over a year ago

Hi, since I posted my Question above, I did make some progress and you are right about the parenthesis around the select to fix the syntax error. So multiple rows are updated and I have to put record_id matching in the where clause. The drivetime_isochrones_IRFAN function returns RETURNS TABLE(record_id_isochrone integer, the_geom_isochrone geometry) and it accepts 4 arguments--so you fixed that too. I have accepted your Answer because that would work per the Question. I will post my Answer later once I have it working. Thank you!!

IrfanClemson · Accepted Answer · 2021-06-26 12:16:44Z

So this is what works for me. I had accepted @jjanes Answer above because that would work based on the original question. But for my need, multiple rows needed to be update. I guess there is better way to do this but I have only about 300+ rows and so manageable.

HTH.

do $$
declare 
f record; 
returned_geometry geometry; 
begin   
    for f in SELECT record_id, clinic_long, clinic_lat FROM joa_clinics
    loop        
        raise notice ' % | %',  f.clinic_long, f.clinic_lat;    
        select t1.the_geom_isochrone
        into returned_geometry
        from dblink('myconn', format('SELECT the_geom_isochrone FROM drivetime_isochrones_IRFAN(%L, %L, %L, %L)', f.clinic_long,f.clinic_lat,8046,false)) 
         as t1 (the_geom_isochrone geometry);
        UPDATE joa_clinics SET precalc_isochrones_5_miles = returned_geometry where joa_clinics.record_id = f.record_id;        
    end loop;
end;
$$

Collectives™ on Stack Overflow

Postgresql: Update Records from a Function Calls from dblink(): Syntax Error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related