pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

using copy from in function
3+ messages / 3 participants
[nested] [flat]

* using copy from in function
@ 2008-03-04 22:51 Yura Gal <yuragal@gmail.com>
  2008-03-05 01:02 ` Re: using copy from in function Osvaldo Kussama <osvaldo.kussama@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Yura Gal @ 2008-03-04 22:51 UTC (permalink / raw)
  To: 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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: using copy from in function
  2008-03-04 22:51 using copy from in function Yura Gal <yuragal@gmail.com>
@ 2008-03-05 01:02 ` Osvaldo Kussama <osvaldo.kussama@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Osvaldo Kussama @ 2008-03-05 01:02 UTC (permalink / raw)
  To: Yura Gal <yuragal@gmail.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: using copy from in function
@ 2008-03-05 17:38 Steve Midgley <public@misuse.org>
  0 siblings, 0 replies; 3+ messages in thread

From: Steve Midgley @ 2008-03-05 17:38 UTC (permalink / raw)
  To: pgsql-sql-owner@postgresql.org; pgsql-sql

At 03:20 AM 3/5/2008, pgsql-sql-owner@postgresql.org wrote:
>Date: Wed, 5 Mar 2008 01:51:19 +0300
>From: "Yura Gal" <yuragal@gmail.com>
>To: pgsql-sql@postgresql.org
>Subject: using copy from in function
>Message-ID: 
><3b6c69d80803041451i6d4d358dk53e9b789bfacd40e@mail.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';
[snip]
>   _file := $$c:\folder1\folder2\$$ || _chrom || '.txt';

Hi,

I'm not sure if this is related, but I have had terrible trouble using 
"\" marks for paths in WinXP.. I have found surprisingly that "/" work 
and don't cause any parsing problems. Also, I believe that if you use 
syntax like:

'/folder1/folder2/' || _chrom || '.txt'

(i.e. leaving off the "c:" part too), you may find that everything just 
works a little cleaner / fewer unexpected surprises.

Like I said, I don't know if this is your issue (and Vista), but it's 
been my experience with WinXP and file paths in Postgresql.

Best,

Steve




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2008-03-05 17:38 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-04 22:51 using copy from in function Yura Gal <yuragal@gmail.com>
2008-03-05 01:02 ` Osvaldo Kussama <osvaldo.kussama@gmail.com>
2008-03-05 17:38 Re: using copy from in function Steve Midgley <public@misuse.org>

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