Stored procedure and returning clause

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Stored procedure and returning clause

Mike Martin
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$
EXECUTE 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

thanks