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@[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@~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/>