Fwd: copy from csv, variable filename within a function

From: basti <black(dot)fledermaus(at)arcor(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: Fwd: copy from csv, variable filename within a function
Date: 2013-04-18 08:34:49
Message-ID: 516FB029.2080607@arcor.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(dot)fledermaus(at)arcor(dot)de>
An: pgsql-sql(at)postgresql(dot)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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Marcin Krawczyk 2013-04-18 10:59:49 ALTER USER abc PASSWORD - what's going on ???
Previous Message basti 2013-04-18 07:26:09 copy from csv, variable filename within a function