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> 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