Interesting COPY edge case...

Just ran into a fascinating edge case. One of our folks was building
a stored function, and ran into an odd error when trying to COPY to
stdout.

Here's a characteristic example:

create or replace function build_table (integer) returns integer as '
begin
execute ''copy foo to stdout;'';
return 1;
end' language plpgsql;

The result:

/* cbbrowne(at)[local]/dba2 in=*/ select try_copy(1);
ERROR: unexpected error -2 in EXECUTE of query "copy dups to stdout;"
CONTEXT: PL/pgSQL function "try_copy" line 2 at execute statement

If I instead create

create or replace function build_table (integer) returns integer as '
copy foo to stdout;
return 1;
' language sql;

The latter works fine.

There is evidently Something Strange about the state of stdout when it
is referenced inside a stored procedure.

We can work around this reasonably, but the "unexpected error -2"
elicits some curiosity I'd like to satisfy. (I bounced it off one of
the others that aren't off on vacation, and his reaction was exactly
the same as mine, namely "Hmmm... I'm just not sure what to expect
from that...")

Is there a good reason/excuse to give as to why the pl/pgsql version
of the COPY *shouldn't* work?
--
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>

On Tue, Jul 26, 2005 at 17:56:42 -0400,
Chris Browne <cbbrowne(at)acm(dot)org> wrote:
>
> There is evidently Something Strange about the state of stdout when it
> is referenced inside a stored procedure.

I suspect this is related to trusted PLs not being able to write files.
It does seem like a problem that COPY has access to stdout in one case
and not the other.

Chris Browne <cbbrowne(at)acm(dot)org> writes:
> create or replace function build_table (integer) returns integer as '
> begin
> execute ''copy foo to stdout;'';
> return 1;
> end' language plpgsql;

> The result:

> /* cbbrowne(at)[local]/dba2 in=*/ select try_copy(1);
> ERROR: unexpected error -2 in EXECUTE of query "copy dups to stdout;"
> CONTEXT: PL/pgSQL function "try_copy" line 2 at execute statement

In CVS tip I get

ERROR: cannot COPY to/from client in PL/pgSQL
CONTEXT: PL/pgSQL function "build_table" line 2 at execute statement

There are some protocol-level reasons for not supporting this, IIRC.
You're already inside a SELECT and the client library would probably
get terribly confused.

regards, tom lane