plpgsql and COPY

Started by Joseph Bonnetalmost 25 years ago2 messagesgeneral
Jump to latest
#1Joseph Bonnet
Joseph.Bonnet@Sodebo.fr

Hi,

Y want to use COPY command in a PLpgSQL procedure like this:

CREATE FUNCTION integration_salarie () RETURNS integer AS '
DECLARE

hemp_r record;
i integer := 0;
emp_exist integer := 0;
hnom varchar;
hpnom varchar;
hpos integer := 0;
hcoddep varchar;

BEGIN

drop table tmphoremp;
create table tmphoremp ( MATRI char(10),
NOMPRE char(30),
CCONT char(2),
MDATREC char(8),
DATCLO char(8),
TYPCONT char(3),
SOCPAIE char(2),
HORSECT char(16)
);

COPY tmphoremp FROM ''/home/public/intrasec/import/HOPEMP12.trs'' USING
DELIMITERS ''\t'';

IF NOT FOUND THEN RETURN -1;
ELSE RETURN i;
END IF;

END;

' LANGUAGE 'plpgsql';

Y have always the response : CopyObjet: don't know to copy 610

What's wrong ?

Thanks for your response.
Joseph Bonnet.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Joseph Bonnet (#1)
Re: plpgsql and COPY

"Joseph Bonnet" <Joseph.Bonnet@Sodebo.fr> writes:

Y want to use COPY command in a PLpgSQL procedure like this:
Y have always the response : CopyObjet: don't know to copy 610

Doesn't work in <= 7.0. Update to 7.1.

If you are going to drop and recreate the table each time, you
might also find that you need to invoke the commands via EXECUTE,
not directly. Not sure about that. For utility commands with
no plans, it might work to do it directly as you have here.

regards, tom lane