Skip site navigation (1) Skip section navigation (2)

Spurious errors relating to escaped single quotes

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Spurious errors relating to escaped single quotes
Date: 2011-07-14 11:45:00
Message-ID: 4E1ED6BC.9020709@archidevsys.co.nz (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi,

Using pg 9.1beta3, I was found that running a function generated an 
error relating to escaped single quotes, yet still produced the answer I 
expected!

/////////////// part000.sql script ///////////

DROP TABLE IF EXISTS measurement CASCADE;

CREATE TABLE measurement
(
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);


CREATE TABLE measurement_y2010m11
(
CHECK (logdate >= '2010-11-01' AND logdate < '2010-12-01')
) INHERITS (measurement);

CREATE TABLE measurement_y2010m12(
CHECK (logdate >= '2010-12-01' AND logdate < '2011-01-01')
) INHERITS (measurement);

CREATE TABLE measurement_y2011m01
(
CHECK (logdate >= '2011-01-01' AND logdate < '2011-02-01')
) INHERITS (measurement);

CREATE TABLE measurement_y2011m02
(
CHECK (logdate >= '2011-02-01' AND logdate < '2011-03-01')
) INHERITS (measurement);


CREATE INDEX ON measurement_y2010m11 (logdate);
CREATE INDEX ON measurement_y2010m12 (logdate);
CREATE INDEX ON measurement_y2011m01 (logdate);
CREATE INDEX ON measurement_y2011m02 (logdate);


DROP FUNCTION IF EXISTS measurement_insert_trigger() CASCADE;

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
v_yyyy int;
v_mm int;
v_sql text;
BEGIN
v_yyyy := extract('year' FROM NEW.logdate);
v_mm := extract('month' FROM NEW.logdate) ;
v_sql := 'INSERT INTO measurement_y' ||
v_yyyy ||
'm' ||
v_mm ||
' VALUES (' ||
NEW.city_id || ', ' ||
''\' || NEW.logdate || '\', ' ||
NEW.peaktemp || ', ' ||
NEW.unitsales ||
')';
EXECUTE v_sql;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;


CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

INSERT INTO measurement
(
city_id,
logdate,
peaktemp,
unitsales
)
VALUES
(3, '2011-01-07', 34, 4000);

TABLE measurement;

////////////// output follows ////////////////

gavin=> \c gcf_db
You are now connected to database "gcf_db" as user "gavin"

gcf_db=> \i part000.sql
psql:part000.sql:1: NOTICE: table "measurement" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
psql:part000.sql:38: NOTICE: function measurement_insert_trigger() does 
not exist, skipping
DROP FUNCTION
psql:part000.sql:63: ERROR: syntax error at or near "\"
LINE 16: ''\' || NEW.logdate || '\', ' ||
^

psql:part000.sql:68: ERROR: function measurement_insert_trigger() does 
not exist
INSERT 0 1
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
3 | 2011-01-07 | 34 | 4000
(1 row)

gcf_db=>


Cheers,
Gavin

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-07-14 15:34:54
Subject: Re: BUG #6117: psql -c does not work as expected. a documentation bug? a program bug?
Previous:From: shahnawaz.shaikhDate: 2011-07-14 08:32:01
Subject: Re: BUG #6118: Server doesn't listen

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group