pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcopy from csv, variable filename within a function
3+ messages / 2 participants
[nested] [flat]
* copy from csv, variable filename within a function
@ 2013-04-18 07:26 basti <black.fledermaus@arcor.de>
2013-04-18 08:34 ` Fwd: copy from csv, variable filename within a function basti <black.fledermaus@arcor.de>
0 siblings, 1 reply; 3+ messages in thread
From: basti @ 2013-04-18 07:26 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Fwd: copy from csv, variable filename within a function
2013-04-18 07:26 copy from csv, variable filename within a function basti <black.fledermaus@arcor.de>
@ 2013-04-18 08:34 ` basti <black.fledermaus@arcor.de>
2013-04-18 13:48 ` Re: copy from csv, variable filename within a function bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: basti @ 2013-04-18 08:34 UTC (permalink / raw)
To: 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.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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: copy from csv, variable filename within a function
2013-04-18 07:26 copy from csv, variable filename within a function basti <black.fledermaus@arcor.de>
2013-04-18 08:34 ` Fwd: copy from csv, variable filename within a function basti <black.fledermaus@arcor.de>
@ 2013-04-18 13:48 ` bricklen <bricklen@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: bricklen @ 2013-04-18 13:48 UTC (permalink / raw)
To: basti <black.fledermaus@arcor.de>; +Cc: pgsql-sql
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> <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
>
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-04-18 13:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-18 07:26 copy from csv, variable filename within a function basti <black.fledermaus@arcor.de>
2013-04-18 08:34 ` basti <black.fledermaus@arcor.de>
2013-04-18 13:48 ` bricklen <bricklen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox