Обсуждение: 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