2008/3/4, Yura Gal <[EMAIL PROTECTED]>: > 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
