Set-returning functions are not allowed in UPDATE when using Postgres 11

Question

I have a Postgresql 9.6 (and postgis 2.5) function that calculates a json with parameters based on a geometry of a record. I created a json field and wrote the json calculation output there, and all was well.

UPDATE public.recordtable
SET polygoncalc = public.fn_calculate_polygon(5)
WHERE id = 5;

When I try to run it on Postgres 11.2 , it returns a error:

SQL Error [0A000]:ERROR: set-returning functions are not allowed in UPDATE

Based on this link I understand there has been a change in postgresql, but honestly,

how can I write the json into the field in Postgres 11.2 without getting the error ?

Thank you in advance.

EDIT: I changed the output field to type json and added the function text

the function:

CREATE OR REPLACE FUNCTION public.fn_create_jsn_point(pid double precision)
 RETURNS TABLE(jsn json)
 LANGUAGE plpgsql
AS $function$
DECLARE
    p_id double precision = pid;
    myjson json = null ;

    begin
       return QUERY 
       select row_to_json(finaljson)
            from(
            select
                 ST_x(a.wgs_84_dump_point) as X,
                 ST_y(a.wgs_84_dump_point) as Y
                        (
                             select (st_transform(st_centroid(t.geom),4326))  wgs_84_dump_point
                                 from baserecords t where base_id = p_id                         
                        ) a ) finaljson;
    END;
$function$
;

@JGH I am returning a single polygon in the original function... — jonatr, Feb 16 '20 at 13:36

score 2 · Accepted Answer · answered Feb 16 '20 at 14:29

The function is returning a table. No matter how many rows are returned, it is returning a table, i.e a set.

Since you know a single value will be returned, you can change the function to restrict it:

CREATE OR REPLACE FUNCTION public.fn_create_jsn_point(pid double precision)
 RETURNS json -- <---------------------------- Return a single value
 LANGUAGE plpgsql
AS $function$
DECLARE
    p_id double precision = pid;
    myjson json = null ;

    begin
       return ( -- <---------------------------------- return a single value
         select row_to_json(finaljson)
            from(
            select
                 ST_x(a.wgs_84_dump_point) as X,
                 ST_y(a.wgs_84_dump_point) as Y
                        (
                             select (st_transform(st_centroid(t.geom),4326))  wgs_84_dump_point
                                 from baserecords t where base_id = p_id                         
                        ) a ) finaljson
       );
    END;
$function$
;

OK, it worked. Thanks. So the ERROR was only because I was returning a table which is a "multiple-answer" by default ??? — jonatr, Feb 16 '20 at 14:47

Set-returning functions are not allowed in UPDATE when using Postgres 11

1 Answers1