Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id CECBF2E0203
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 18:51:24 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 48600-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 18:51:22 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.190])
	by postgresql.org (Postfix) with ESMTP id EC6782E0186
	for <pgsql-sql@postgresql.org>; Tue,  4 Mar 2008 18:51:21 -0400 (AST)
Received: by nf-out-0910.google.com with SMTP id f5so660839nfh.23
	for <pgsql-sql@postgresql.org>; Tue, 04 Mar 2008 14:51:20 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	bh=SxPWdbdtktCKpDvpoAoK+hoaaqIXdjJQPN3HHaWo3+E=;
	b=alUJCrkeHTlmArvawZiBWycPIT8IKm9uJjTAYVCWtGlDczqyP4kWumIc1sk172nt0kwBDoSRdcGnU8RApB/eFMANoX7emGqYyjCP/cXphik1UzRdKsCG5+yIOCamOx1mjnDZhZHNlO951PSwd/CRC9z5o/yFoguH/3YvxaGGC8g=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=QMCamd/+785KeueVt77aJxlmoDgaRs2w00gNCt/affMwPDOt3zzPNMEV6omlHjOKbpWyctr15T23TuA14gPf1qgk0qQF/KQVCi5MEb2gJL58TzGnGBSqaf9yxDZGJQ+dhQvEURCk4DCfPJqB9omZf6YXlTiruOgVvqNKDjP5HBo=
Received: by 10.78.100.1 with SMTP id x1mr4827278hub.47.1204671079920;
	Tue, 04 Mar 2008 14:51:19 -0800 (PST)
Received: by 10.78.202.12 with HTTP; Tue, 4 Mar 2008 14:51:19 -0800 (PST)
Message-ID: <3b6c69d80803041451i6d4d358dk53e9b789bfacd40e@mail.gmail.com>
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
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200803/13
X-Sequence-Number: 30341

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