From: Osvaldo Kussama <osvaldo.kussama@gmail.com>
To: Yura Gal <yuragal@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: using copy from in function
Date: Tue, 4 Mar 2008 22:02:55 -0300
Message-ID: <690707f60803041702j738a2b4et132d4ff2913ebecf@mail.gmail.com> (raw)
In-Reply-To: <3b6c69d80803041451i6d4d358dk53e9b789bfacd40e@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: osvaldo.kussama@gmail.com, yuragal@gmail.com
Subject: Re: using copy from in function
In-Reply-To: <690707f60803041702j738a2b4et132d4ff2913ebecf@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox