Обсуждение: copy from csv, variable filename within a function

Поиск

Список

Период

Сортировка

copy from csv, variable filename within a function

От

basti

Дата:

18 апреля 2013 г., 07:26:27

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

Fwd: copy from csv, variable filename within a function

От

basti

Дата:

18 апреля 2013 г., 08:34:57

<tt>I have fixed it with dollar-quoting.</tt><tt><br /></tt><div class="moz-forward-container"><tt><br
/></tt><tt>--------Original-Nachricht --------</tt><table border="0" cellpadding="0" cellspacing="0"
class="moz-email-headers-table"><tbody><tr><thalign="RIGHT" nowrap valign="BASELINE"><tt>Betreff:
</tt></th><td><tt>[SQL]copy from csv, variable filename within a function</tt></td></tr><tr><th align="RIGHT" nowrap
valign="BASELINE"><tt>Datum:</tt></th><td><tt>Thu, 18 Apr 2013 09:26:09 +0200</tt></td></tr><tr><th align="RIGHT"
nowrapvalign="BASELINE"><tt>Von: </tt></th><td><tt>basti <a class="moz-txt-link-rfc2396E"
href="mailto:black.fledermaus@arcor.de"><black.fledermaus@arcor.de></a></tt></td></tr><tr><thalign="RIGHT" nowrap
valign="BASELINE"><tt>An:</tt></th><td><tt><a class="moz-txt-link-abbreviated"
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a></tt></td></tr></tbody></table><tt><br/></tt><tt><br
/></tt><pre>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
precisionNOT 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 (<a class="moz-txt-link-abbreviated"
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)
To make changes to your subscription:
<a class="moz-txt-link-freetext"
href="http://www.postgresql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</a>
</pre><br /></div><tt><br /></tt>

Re: copy from csv, variable filename within a function

От

bricklen

Дата:

18 апреля 2013 г., 13:48:36

Note: "DELEMITER" should be "DELIMITER".

On Thu, Apr 18, 2013 at 1:34 AM, basti <black.fledermaus@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.fledermaus@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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: copy from csv, variable filename within a function

copy from csv, variable filename within a function

Fwd: copy from csv, variable filename within a function

Re: copy from csv, variable filename within a function