Thread: BUG #6213: COPY does not work as expected in a plpgsql function
The following bug has been logged online:
Bug reference: 6213
Logged by: Ramanujam
Email address: innomotive@gmail.com
PostgreSQL version: 9.0
Operating system: linux x86_64
Description: COPY does not work as expected in a plpgsql function
Details:
A function like this:
CREATE OR REPLACE FUNCTION test(parm character varying(3)) RETURNS SETOF
integer AS
$BODY$
BEGIN
COPY (SELECT $1) TO '/tmp/test.txt' CSV;
END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100 ROWS 100;
Throws an error:
NUM:42P02, DETAILS:there is no parameter $1
Substrituting $1 with parm gives this error:
NUM:42703, DETAILS:column "parm" does not exist
On Sun, Sep 18, 2011 at 10:55 PM, Ramanujam <innomotive@gmail.com> wrote: > The following bug has been logged online: > > Bug reference: =A0 =A0 =A06213 > Logged by: =A0 =A0 =A0 =A0 =A0Ramanujam > Email address: =A0 =A0 =A0innomotive@gmail.com > PostgreSQL version: 9.0 > Operating system: =A0 linux x86_64 > Description: =A0 =A0 =A0 =A0COPY does not work as expected in a plpgsql f= unction > Details: > > A function like this: > > CREATE OR REPLACE FUNCTION test(parm character varying(3)) RETURNS SETOF > integer AS > $BODY$ > > BEGIN > =A0 =A0COPY (SELECT $1) TO '/tmp/test.txt' CSV; > END; > > $BODY$ > LANGUAGE plpgsql VOLATILE COST 100 ROWS 100; > > Throws an error: > NUM:42P02, DETAILS:there is no parameter $1 > > Substrituting $1 with parm gives this error: > NUM:42703, DETAILS:column "parm" does not exist Does it work if you use EXECUTE? --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Sep 26, 2011 at 9:57 AM, Robert Haas <robertmhaas@gmail.com> wrote: > Does it work if you use EXECUTE? Apologies to have not included the list when I replied to Pavel. Re-writing it as a dynamic sql stmt indeed works. Sorry for the noise.
On Mon, Sep 26, 2011 at 12:45 AM, Ramanujam <innomotive@gmail.com> wrote: > On Mon, Sep 26, 2011 at 9:57 AM, Robert Haas <robertmhaas@gmail.com> wrote: >> Does it work if you use EXECUTE? > > Apologies to have not included the list when I replied to Pavel. > Re-writing it as a dynamic sql stmt indeed works. Sorry for the noise. Ah, OK. No problem. It would actually be nice if worked even without that, but I'm not sure what would be involved in making that happen. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> It would actually be nice if worked even without that, but I'm not
> sure what would be involved in making that happen.
I've been too busy to look at this in detail, but I imagine the issue is
failure to pass parameters down from the ProcessUtility call to COPY
into the parsing/execution of the sub-SELECT. It might be relatively
straightforward to fix, or then again it might not. The parsing end of
it could quite likely be harder than the execution end. We've
surmounted similar issues in places like EXPLAIN, though.
regards, tom lane