Thread: plpgsql and COPY

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.

"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