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

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

From: "Silvio Macedo" <smacedo(at)calmetric(dot)pt>
To: <pgsql-interfaces(at)postgresql(dot)org>
Subject: COPY FROM STDIN fails inside Pl/pgSQL function or in PgAdmin , but ok in psql
Date: 2005-08-29 18:43:05
Message-ID: 000501c5acc9$83747aa0$0501a8c0@plexus (view raw or flat)
Thread:
Lists: pgsql-interfaces
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


Responses

pgsql-interfaces by date

Next:From: Chris BrowneDate: 2005-08-29 22:16:26
Subject: Re: COPY FROM STDIN fails inside Pl/pgSQL function or in PgAdmin , but ok in psql
Previous:From: Kuba OuhrabkaDate: 2005-08-29 08:45:16
Subject: Re: ecpg: arrays and nulls

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