Stored procedure with execute and returning clause

From: Mike Martin <redtux1(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Stored procedure with execute and returning clause
Date: 2020-08-23 12:35:54
Message-ID: CAOwYNKbwEiMjDCz6kJh-r_s-8_PgGvv6nMUi2BvfQVT2f_uzdQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2020-08-23 12:47:37 Re: Stored procedure with execute and returning clause
Previous Message Dirk Krautschick 2020-08-22 22:32:45 Getting away from Oracle APEX, recommendations for PostgreSQL?