From: Silvio Macedo <smacedo@calmetric.pt>
To: pgsql-interfaces@postgresql.org
Subject: COPY FROM STDIN fails inside Pl/pgSQL function or in PgAdmin , but ok in psql
Date: Mon, 29 Aug 2005 19:43:05 +0100
Message-ID: <000501c5acc9$83747aa0$0501a8c0@plexus> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: smacedo@calmetric.pt
Subject: Re: COPY FROM STDIN fails inside Pl/pgSQL function or in PgAdmin , but ok in psql
In-Reply-To: <000501c5acc9$83747aa0$0501a8c0@plexus>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox