Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1USkJ0-00089j-UK for pgsql-sql@arkaria.postgresql.org; Thu, 18 Apr 2013 08:34:59 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1USkJ0-0008Br-B7 for pgsql-sql@arkaria.postgresql.org; Thu, 18 Apr 2013 08:34:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1USkIy-0008A2-Bl for pgsql-sql@postgresql.org; Thu, 18 Apr 2013 08:34:56 +0000 Received: from mail-in-02.arcor-online.net ([151.189.21.42]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1USkIu-00059j-2j for pgsql-sql@postgresql.org; Thu, 18 Apr 2013 08:34:55 +0000 Received: from mail-in-03-z2.arcor-online.net (mail-in-03-z2.arcor-online.net [151.189.8.15]) by mx.arcor.de (Postfix) with ESMTP id DC967310B3 for ; Thu, 18 Apr 2013 10:34:50 +0200 (CEST) Received: from mail-in-18.arcor-online.net (mail-in-18.arcor-online.net [151.189.21.58]) by mail-in-03-z2.arcor-online.net (Postfix) with ESMTP id D9ED1562EBB for ; Thu, 18 Apr 2013 10:34:50 +0200 (CEST) X-Greylist: Passed host: 80.153.9.217 X-DKIM: Sendmail DKIM Filter v2.8.2 mail-in-18.arcor-online.net A97E53DC321 DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=arcor.de; s=mail-in; t=1366274090; bh=Sa0L9+TXsjptspJMstgkwO52AWdRLuZI5KL8/gI/DZM=; h=Message-ID:Date:From:MIME-Version:To:Subject:References: In-Reply-To:Content-Type; b=B3nwqdZTAmjH0XKOz2RfesiqXY2r+ACNSy+jRMOmzJawk+7nZynrwtqzQPC0FvH2q 32GGwoZ68kppwgzd/tey0SF3DbTS+PNaGxrZgzJATYx+E/bscE6dZrvc/ixsEq3Sww 3TnzPBNSEP52ljT3jqE6gboAZYXDoiTaJ564gtp8= Received: from [192.168.30.136] (p509909d9.dip0.t-ipconnect.de [80.153.9.217]) (Authenticated sender: black.fledermaus@arcor.de) by mail-in-18.arcor-online.net (Postfix) with ESMTPA id A97E53DC321 for ; Thu, 18 Apr 2013 10:34:50 +0200 (CEST) Message-ID: <516FB029.2080607@arcor.de> Date: Thu, 18 Apr 2013 10:34:49 +0200 From: basti User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20130328 Thunderbird/17.0.5 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Fwd: copy from csv, variable filename within a function References: <516FA011.3050407@arcor.de> In-Reply-To: <516FA011.3050407@arcor.de> X-Forwarded-Message-Id: <516FA011.3050407@arcor.de> Content-Type: multipart/alternative; boundary="------------060506050408070003020703" X-Pg-Spam-Score: -1.8 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------060506050408070003020703 Content-Type: text/plain; charset=ISO-8859-15 Content-Transfer-Encoding: quoted-printable 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: pgsql-sql@postgresql.org Hello, i have try the following: -- Function: wetter.copy_ignore_duplicate(character varying) =20 -- DROP FUNCTION wetter.copy_ignore_duplicate(character varying); =20 CREATE OR REPLACE FUNCTION wetter.copy_ignore_duplicate(_filename character varying) RETURNS void AS $BODY$ declare sql text; =20 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; =20 =20 --copy tmp_raw_data( -- "timestamp", temp_in, pressure, temp_out, humidity, wdir, wspeed) =20 --FROM '/home/wetter/csv/data/raw/2013/2013-04/2013-04-16.txt' --WITH DELIMITER ','; =20 sql :=3D 'COPY tmp_raw_data( -- "timestamp", temp_in, pressure, temp_out, humidity, wdir, wspeed) FROM ' || quote_literal(_filename) || 'WITH DELEMITER ',' '; execute sql; =20 -- 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) =20 SELECT "timestamp", temp_in, pressure, temp_out, humidity, wdir, wspee= d FROM tmp_raw_data WHERE NOT EXISTS (SELECT 1 FROM wetter.raw_data WHERE raw_data.timestamp =3D 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' ); ################################# ################################# =20 =20 HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen Index =BBtmp_raw_data_pkey=AB f=FCr Tabelle =BBtmp_raw_data=AB CONTEXT: SQL-Anweisung =BBCREATE 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=AB PL/pgSQL function "copy_ignore_duplicate" line 4 at SQL-Anweisung FEHLER: Anfrage =BBSELECT 'COPY tmp_raw_data( -- "timestamp", temp_in, pressure, temp_out, humidity, wdir, wspeed) FROM ' || quote_literal( $1 ) || 'WITH DELEMITER ',' '=AB hat 2 Spalten zur=FCckgegeben CONTEXT: PL/pgSQL-Funktion =BBcopy_ignore_duplicate=AB Zeile 29 bei Zuwe= isung =20 ********** Fehler ********** =20 FEHLER: Anfrage =BBSELECT 'COPY tmp_raw_data( -- "timestamp", temp_in, pressure, temp_out, humidity, wdir, wspeed) FROM ' || quote_literal( $1 ) || 'WITH DELEMITER ',' '=AB hat 2 Spalten zur=FCckgegeben SQL Status:42601 Kontext:PL/pgSQL-Funktion =BBcopy_ignore_duplicate=AB Zeile 29 bei Zuweis= ung --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql --------------060506050408070003020703 Content-Type: text/html; charset=ISO-8859-15 Content-Transfer-Encoding: quoted-printable 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><= /td>
An: pgsql-sql@postgresql.org 


Hello,
i have try the following:

-- Function: wetter.copy_ignore_duplicate(character varying)
=20
-- DROP FUNCTION wetter.copy_ignore_duplicate(character varying);
=20
CREATE OR REPLACE FUNCTION wetter.copy_ignore_duplicate(_filename
character varying)
  RETURNS void AS
$BODY$
declare sql text;
=20
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;
=20
=20
--copy tmp_raw_data(
--           "timestamp", temp_in, pressure, temp_out, humidity, wdir,
wspeed)
=20
--FROM '/home/wetter/csv/data/raw/2013/2013-04/2013-04-16.txt'
--WITH DELIMITER ',';
=20
sql :=3D 'COPY  tmp_raw_data(
--            "timestamp", temp_in, pressure, temp_out, humidity, wdir,
wspeed) FROM ' || quote_literal(_filename) || 'WITH DELEMITER ',' ';
execute sql;
=20
-- 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)
      =20
   SELECT "timestamp", temp_in, pressure, temp_out, humidity, wdir, wspee=
d
   FROM tmp_raw_data
   WHERE NOT EXISTS (SELECT 1 FROM wetter.raw_data
                     WHERE raw_data.timestamp =3D 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'
);
#################################
#################################
=20
=20
HINWEIS:  CREATE TABLE / PRIMARY KEY erstellt implizit einen Index
=BBtmp_raw_data_pkey=AB f=FCr Tabelle =BBtmp_raw_data=AB
CONTEXT:  SQL-Anweisung =BBCREATE 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=AB
PL/pgSQL function "copy_ignore_duplicate" line 4 at SQL-Anweisung
FEHLER:  Anfrage =BBSELECT  'COPY  tmp_raw_data(
--            "timestamp", temp_in, pressure, temp_out, humidity, wdir,
wspeed) FROM ' || quote_literal( $1 ) || 'WITH DELEMITER ',' '=AB hat 2
Spalten zur=FCckgegeben
CONTEXT:  PL/pgSQL-Funktion =BBcopy_ignore_duplicate=AB Zeile 29 bei Zuwe=
isung
=20
********** Fehler **********
=20
FEHLER: Anfrage =BBSELECT  'COPY  tmp_raw_data(
--            "timestamp", temp_in, pressure, temp_out, humidity, wdir,
wspeed) FROM ' || quote_literal( $1 ) || 'WITH DELEMITER ',' '=AB hat 2
Spalten zur=FCckgegeben
SQL Status:42601
Kontext:PL/pgSQL-Funktion =BBcopy_ignore_duplicate=AB Zeile 29 bei Zuweis=
ung



--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


--------------060506050408070003020703--