Re: COPY FROM STDIN fails inside Pl/pgSQL function or in PgAdmin , but ok in psql

Hi,

(Pg 8.0.3 on Windows XP SP2, client psql, ODBC and pgAdmin )

Summary: COPY FROM STDIN works on psql, but not inside a Pl/pgSQL
function, nor a pgAdmin SQL window, and not on a ODBC command...

I would appreciate if anybody could explain me why this doesn't work:

-- create table
CREATE TABLE temp_data (
xx float,
yy float,
ww float,
hh float) WITHOUT OIDS;

-- create function
CREATE OR REPLACE FUNCTION check_data(text) RETURNS integer AS $func$
BEGIN

EXECUTE 'COPY temp_data(xx,yy,ww,hh) FROM STDIN WITH DELIMITER ''|'';'
||'\n' || $1 || '\n\\.';

-- just to get something out
return length($1);
END;
$func$ LANGUAGE plpgsql;

and then:

select check_data('1.0|2.0|3.0|4.0');

It gives out:
ERROR: syntax error at or near "1.0" at character 60
QUERY: COPY temp_data(xx,yy,ww,hh) FROM STDIN WITH DELIMITER '|';
1.0|2.0|3.0|4.0
\.
CONTEXT: PL/pgSQL function "check_data" line 8 at execute statement

Actually, if I do the COPY within a pgAdmin SQL command prompt, it
also fails. For example, the following, alone, without any plpgsql -

COPY temp_data(xx,yy,ww,hh) FROM STDIN WITH DELIMITER '|';
1.0|2.0|3.0|4.0
\.
will fail.

If on the other hand, I execute these lines on a psql prompt, it works
just fine.

Any help will be appreciated.

Regards
Silvio

smacedo(at)calmetric(dot)pt ("Silvio Macedo") writes:
> (Pg 8.0.3 on Windows XP SP2, client psql, ODBC and pgAdmin )
>
> Summary: COPY FROM STDIN works on psql, but not inside a Pl/pgSQL
> function, nor a pgAdmin SQL window, and not on a ODBC command...

I believe that it's because inside "those places," you don't have
either a stdin or a stdout.

One of our folks ran into the same situation, and the same surprise.

See:
<http://archives.postgresql.org/pgsql-hackers/2005-07/msg00949.php>
<http://archives.postgresql.org/pgsql-hackers/2005-07/msg01067.php>
<http://archives.postgresql.org/pgsql-hackers/2005-07/msg00997.php>

Bruno Wolfe suggests the problem is because pl/pgsql is considered a
"trusted" language, which thus shouldn't be able to read/write files.

In a more recent CVS, Tom points out that this elicits a more
meaningful error message:

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

He also points out that this is taking place inside a SELECT, and that
the client library would likely get confused at this 'abuse' of the
protocol...
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/emacs.html
Rules of the Evil Overlord #127. "Prison guards will have their own
cantina featuring a wide variety of tasty treats that will deliver
snacks to the guards while on duty. The guards will also be informed
that accepting food or drink from any other source will result in
execution." <http://www.eviloverlord.com/>