Note: "DELEMITER" should be "DELIMITER".
On Thu, Apr 18, 2013 at 1:34 AM, basti <black.flederm...@arcor.de> wrote: > I have fixed it with dollar-quoting. > > -------- Original-Nachricht -------- Betreff: [SQL] copy from csv, > variable filename within a function Datum: Thu, 18 Apr 2013 09:26:09 > +0200 Von: basti <black.flederm...@arcor.de> <black.flederm...@arcor.de> An: > pgsql-sql@postgresql.org > > > Hello, > i have try the following: > > -- Function: wetter.copy_ignore_duplicate(character varying) > > -- DROP FUNCTION wetter.copy_ignore_duplicate(character varying); > > CREATE OR REPLACE FUNCTION wetter.copy_ignore_duplicate(_filename > character varying) > RETURNS void AS > $BODY$ > declare sql text; > > 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; > > > --copy tmp_raw_data( > -- "timestamp", temp_in, pressure, temp_out, humidity, wdir, > wspeed) > > --FROM '/home/wetter/csv/data/raw/2013/2013-04/2013-04-16.txt' > --WITH DELIMITER ','; > > sql := 'COPY tmp_raw_data( > -- "timestamp", temp_in, pressure, temp_out, humidity, wdir, > wspeed) FROM ' || quote_literal(_filename) || 'WITH DELEMITER ',' '; > execute sql; > > -- 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) > > 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 = 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' > ); > ################################# > ################################# > > > HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen Index > »tmp_raw_data_pkey« für Tabelle »tmp_raw_data« > CONTEXT: SQL-Anweisung »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« > PL/pgSQL function "copy_ignore_duplicate" line 4 at SQL-Anweisung > FEHLER: Anfrage »SELECT 'COPY tmp_raw_data( > -- "timestamp", temp_in, pressure, temp_out, humidity, wdir, > wspeed) FROM ' || quote_literal( $1 ) || 'WITH DELEMITER ',' '« hat 2 > Spalten zurückgegeben > CONTEXT: PL/pgSQL-Funktion »copy_ignore_duplicate« Zeile 29 bei Zuweisung > > ********** Fehler ********** > > FEHLER: Anfrage »SELECT 'COPY tmp_raw_data( > -- "timestamp", temp_in, pressure, temp_out, humidity, wdir, > wspeed) FROM ' || quote_literal( $1 ) || 'WITH DELEMITER ',' '« hat 2 > Spalten zurückgegeben > SQL Status:42601 > Kontext:PL/pgSQL-Funktion »copy_ignore_duplicate« Zeile 29 bei Zuweisung > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-sql > > > >