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

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!

Solution

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.