DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=oEDEcjf4JA33pebsqOeCQC2u9jA1C45rZPg9v83uWD7FGEshrRQaRJO95RUMqlGTRnhhhO+R/jcoFkqPupmHuJ58PfvVy9h0QwrAsmsd8KkcfzrkMOfEByxIDZgGMhNdXW/95uphUxJjR+H8CFO5bxLAlRsEX/DR/Sp4y3G78eM=
Message-ID: <690707f60803041702j738a2b4et132d4ff2913ebecf@mail.gmail.com>
Date: Tue, 4 Mar 2008 22:02:55 -0300
From: "Osvaldo Kussama" <osvaldo.kussama@gmail.com>
To: "Yura Gal" <yuragal@gmail.com>
Subject: Re: using copy from in function
Cc: pgsql-sql@postgresql.org
In-Reply-To: <3b6c69d80803041451i6d4d358dk53e9b789bfacd40e@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <3b6c69d80803041451i6d4d358dk53e9b789bfacd40e@mail.gmail.com>

2008/3/4, Yura Gal <yuragal@gmail.com>:
> I'm trying to automate import data using CORY FROM. For this purpose I
>  wrote plpgsql function. As my postgres works on windows vista I need
>  to use E'' syntax for path-to-file. This syntax works fine in SQL
>  queries like:
>  COPY table FROM E'path_to_file_with_double_backslashes';
>
>  Following query works fine too:
>  COPY table FROM $$path_to_file_with_single_backslashes$$;
>
>  However I can't figure it out how to use file_path variable as this
>  code throw error while compilation:
>
>  CREATE OR REPLACE FUNCTION func (inout _chrom varchar) RETURNS varchar AS
>  $body$
>  DECLARE
>   _chrom ALIAS FOR $1;
>   _file TEXT;
>  BEGIN
>   _file := $$c:\folder1\folder2\$$ || _chrom || '.txt';
>   RAISE NOTICE 'Filename is -> %', _file;
>   COPY table (column) FROM _file CSV HEADER;
>
>   statements;
>
>  EXCEPTION
>  WHEN bad_copy_file_format THEN
>         _chrom := 'badformat';
>  WHEN io_error THEN
>         _chrom := 'ioerr';
>  WHEN undefined_file THEN
>         _chrom := 'unfile';
>         RETURN;
>  END;
>  $body$
>  LANGUAGE 'plpgsql' ;
>
>  ERROR: syntax error in query at or near "$1" at character 35
>  QUERY:   COPY table (column) FROM $1 CSV HEADER
>  CONTEXT: SQL statement in PL/PgSQL function "func" near line 7
>
>  Is there any tricks to solve this problem? I suspect that something
>  wrong with quotation but can not find out a mistake.
>
>  PS. Postgres server version is 8.3
>
>


Try:
EXECUTE 'COPY table (column) FROM ' || _file || 'CSV HEADER;';

Osvaldo