From: | "Yura Gal" <yuragal(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | using copy from in function |
Date: | 2008-03-04 22:51:19 |
Message-ID: | 3b6c69d80803041451i6d4d358dk53e9b789bfacd40e@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Osvaldo Kussama | 2008-03-05 01:02:55 | Re: using copy from in function |
Previous Message | Mary Anderson | 2008-03-04 22:13:02 | Re: Documenting a DB schema |