Message-ID: <516FA011.3050407@arcor.de>
Date: Thu, 18 Apr 2013 09:26:09 +0200
From: basti <black.fledermaus@arcor.de>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:17.0) Gecko/20130328 Thunderbird/17.0.5
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: copy from csv, variable filename within a function
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Hello,
i have try the following:

-- Function: wetter.copy_ignore_duplicate(character varying)
=20
-- DROP FUNCTION wetter.copy_ignore_duplicate(character varying);
=20
CREATE OR REPLACE FUNCTION wetter.copy_ignore_duplicate(_filename
character varying)
  RETURNS void AS
$BODY$
declare sql text;
=20
BEGIN
CREATE TEMP TABLE tmp_raw_data
(
  "timestamp" timestamp without time zone NOT NULL,
  temp_in double precision NOT NULL,
  pressure double precision NOT NULL,
  temp_out double precision NOT NULL,
  humidity double precision NOT NULL,
  wdir integer NOT NULL,
  wspeed double precision NOT NULL,
  CONSTRAINT tmp_raw_data_pkey PRIMARY KEY ("timestamp")
)
ON COMMIT DROP;
=20
=20
--copy tmp_raw_data(
--           "timestamp", temp_in, pressure, temp_out, humidity, wdir,
wspeed)
=20
--FROM '/home/wetter/csv/data/raw/2013/2013-04/2013-04-16.txt'
--WITH DELIMITER ',';
=20
sql :=3D 'COPY  tmp_raw_data(
--            "timestamp", temp_in, pressure, temp_out, humidity, wdir,
wspeed) FROM ' || quote_literal(_filename) || 'WITH DELEMITER ',' ';
execute sql;
=20
-- prevent any other updates while we are merging input (omit this if
you don't need it)
LOCK wetter.raw_data IN SHARE ROW EXCLUSIVE MODE;
-- insert into raw_data table
INSERT INTO wetter.raw_data(
            "timestamp", temp_in, pressure, temp_out, humidity, wdir,
wspeed)
=20=20=20=20=20=20=20
   SELECT "timestamp", temp_in, pressure, temp_out, humidity, wdir, wspeed
   FROM tmp_raw_data
   WHERE NOT EXISTS (SELECT 1 FROM wetter.raw_data
                     WHERE raw_data.timestamp =3D tmp_raw_data.timestamp);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION wetter.copy_ignore_duplicate(character varying)
  OWNER TO postgres;


But when i execute it i get the this error:
(sorry i don't know how to switch the error messages to English lang)
I think this a problem with escaping the delimiter


SELECT wetter.copy_ignore_duplicate(
    '/home/wetter/csv/data/raw/2013/2013-04/2013-04-16.txt'
);
#################################
#################################
=20
=20
HINWEIS:  CREATE TABLE / PRIMARY KEY erstellt implizit einen Index
=BBtmp_raw_data_pkey=AB f=FCr Tabelle =BBtmp_raw_data=AB
CONTEXT:  SQL-Anweisung =BBCREATE TEMP TABLE tmp_raw_data ( "timestamp"
timestamp without time zone NOT NULL, temp_in double precision NOT NULL,
pressure double precision NOT NULL, temp_out double precision NOT NULL,
humidity double precision NOT NULL, wdir integer NOT NULL, wspeed double
precision NOT NULL, CONSTRAINT tmp_raw_data_pkey PRIMARY KEY
("timestamp") ) ON COMMIT DROP=AB
PL/pgSQL function "copy_ignore_duplicate" line 4 at SQL-Anweisung
FEHLER:  Anfrage =BBSELECT  'COPY  tmp_raw_data(
--            "timestamp", temp_in, pressure, temp_out, humidity, wdir,
wspeed) FROM ' || quote_literal( $1 ) || 'WITH DELEMITER ',' '=AB hat 2
Spalten zur=FCckgegeben
CONTEXT:  PL/pgSQL-Funktion =BBcopy_ignore_duplicate=AB Zeile 29 bei Zuweis=
ung
=20
********** Fehler **********
=20
FEHLER: Anfrage =BBSELECT  'COPY  tmp_raw_data(
--            "timestamp", temp_in, pressure, temp_out, humidity, wdir,
wspeed) FROM ' || quote_literal( $1 ) || 'WITH DELEMITER ',' '=AB hat 2
Spalten zur=FCckgegeben
SQL Status:42601
Kontext:PL/pgSQL-Funktion =BBcopy_ignore_duplicate=AB Zeile 29 bei Zuweisung


--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql