From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Mike Martin <redtux1(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Stored procedure with execute and returning clause |
Date: | 2020-08-23 12:47:37 |
Message-ID: | CAFj8pRBtzZXv2EzZiVVZ-YO4VOZcjzDbSoBKj6uq0DPqnD5u=g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
ne 23. 8. 2020 v 14:36 odesílatel Mike Martin <redtux1(at)gmail(dot)com> napsal:
>
> 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
>
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
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Förster | 2020-08-23 13:10:35 | has_database_privilege is true? |
Previous Message | Mike Martin | 2020-08-23 12:35:54 | Stored procedure with execute and returning clause |