[SQL] copy from csv, variable filename within a function
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Fwd: [SQL] copy from csv, variable filename within a function
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 An: [email protected] 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] apt.postgresql.org vs. Pitti PPA - install error
Please post your /etc/apt/source.list entry and "lsb_release -c" Am 18.06.2013 12:12, schrieb rawi: > Working on Ubuntu 13.04 64bit (raring) > > I red, that Pitti-PPA will be discontinued in the future, so we should make > the switch to apt.postgresql.org. > > But trying to "aptitude install postgresql-9.3" (and with 9.2 - the same) I > get an install error: > > > The following packages have unmet dependencies: > postgresql-common : Breaks: logrotate (>= 3.8) but 3.8.3-3ubuntu2 is > installed. > > > Going back to the Pitti PPA I could install pg-9.2 without problems (there > will be no 9.3 in there) > > Regards > Rawi > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/apt-postgresql-org-vs-Pitti-PPA-install-error-tp5759622.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
