Skip site navigation (1) Skip section navigation (2)

Interesting COPY edge case...

From: Chris Browne <cbbrowne(at)acm(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Interesting COPY edge case...
Date: 2005-07-26 21:56:42
Message-ID: 60ll3t1bad.fsf@dba2.int.libertyrms.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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/>

Responses

pgsql-hackers by date

Next:From: Jochem van DietenDate: 2005-07-26 22:11:47
Subject: Re: ENUM type
Previous:From: Simon RiggsDate: 2005-07-26 21:56:19
Subject: Re: Checkpoint cost, looks like it is WAL/CRC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group