Thread: Stored procedure with execute and returning clause
Hi I am having difficulty with returning clause and stored procedure. This is an (edited) example of where I am CREATE OR REPLACE PROCEDURE public.arrcopy1( dataarr anyarray, tblname text, cols text DEFAULT NULL::text, selstr text DEFAULT NULL::text, INOUT outarr text[] DEFAULT NULL ) LANGUAGE 'plpgsql' AS $BODY$ insstr:= INSERT INTO tagdata(fileid,tagname,tagvalue) SELECT arr[1]::integer,arr[2]::text,string_to_array(arr[3],E'\b') FROM (select array_agg(v order by rn) arr from unnest($1) with ordinality v(v,rn) group by (rn - 1) / array_length($1::text[],2) ) a JOIN tagfile ON fileid=arr[1]::int RETURNING *::text[]; Then called as EXECUTE insstr INTO outarr USING (dataarr) ; $BODY$ This compiles as a proc But I then get an error (this is in perl) DBD::Pg::db selectall_arrayref failed: ERROR: malformed array literal: "3182753" DETAIL: Array value must start with "{" or dimension information The procedure works perfectly without the INTO Clause on execute If I change returning clause to RETURNING array[fileid] It runs but only returns the first fileid not all fileids inserted thanks | |
ne 23. 8. 2020 v 14:36 odesílatel Mike Martin <redtux1@gmail.com> napsal:
HiI am having difficulty with returning clause and stored procedure. This is an (edited) example of where I amCREATE OR REPLACE PROCEDURE public.arrcopy1(
dataarr anyarray,
tblname text,
cols text DEFAULT NULL::text,
selstr text DEFAULT NULL::text,
INOUT outarr text[] DEFAULT NULL
)
LANGUAGE 'plpgsql'
AS $BODY$insstr:= INSERT INTO tagdata(fileid,tagname,tagvalue) SELECT arr[1]::integer,arr[2]::text,string_to_array(arr[3],E'\b') FROM
(select array_agg(v order by rn) arr
from unnest($1) with ordinality v(v,rn)
group by (rn - 1) / array_length($1::text[],2)
) a
JOIN tagfile ON fileid=arr[1]::int RETURNING *::text[];Then called asEXECUTE insstr INTO outarr USING (dataarr) ;$BODY$This compiles as a procBut I then get an error (this is in perl)DBD::Pg::db selectall_arrayref failed: ERROR: malformed array literal: "3182753"
DETAIL: Array value must start with "{" or dimension informationThe procedure works perfectly without the INTO Clause on executeIf I change returning clause toRETURNING array[fileid]It runs but only returns the first fileid not all fileids inserted
I afraid so expression like '*'::text[] is not supported
you need to assign returning value to RECORD value, and then manually build a array from array' fields
There is not any cast record to array.
Regards
Pavel
thanks