From: | salah jubeh <s_jubeh(at)yahoo(dot)com> |
---|---|
To: | fv967 <fv967(at)hotmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Plpgsql 9.1.3 : not accepting "open", "close" as column names |
Date: | 2012-04-22 18:43:51 |
Message-ID: | 1335120231.68766.YahooMailNeo@web161502.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
you need to use quotes because open and close are sql key words
http://www.postgresql.org/docs/9.1/static/sql-keywords-appendix.html
row."open"
row."close"
Regards
________________________________
From: fv967 <fv967(at)hotmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Sent: Sunday, April 22, 2012 11:34 AM
Subject: [GENERAL] Plpgsql 9.1.3 : not accepting "open", "close" as column names
Hi ,
I have the following function which was working fine in Postgresql 8.4.7
CREATE OR REPLACE FUNCTION insert_stockpricemerge1(startdate character
varying, enddate character varying)
RETURNS void AS
$BODY$
DECLARE
row RECORD;
BEGIN
FOR row IN SELECT stockid, date, open, high, low, close, volume FROM
stockpriceretrieve AS r WHERE r.error IS NULL AND r.date BETWEEN
CAST(startdate AS date) AND CAST(enddate AS date) LOOP
BEGIN
INSERT INTO stockpricemerge (stockid, date, open, high, low, close,
volume, occurrence ) VALUES ( row.stockid, row.date, row.open , row.high,
row.low, row.close, row.volume, 1 );
EXCEPTION
WHEN unique_violation THEN
UPDATE stockpricemerge SET occurrence = occurrence + 1
WHERE stockpricemerge.stockid = row.stockid
AND stockpricemerge.date = row.date
AND stockpricemerge.open = row.open
AND stockpricemerge.high = row.high
AND stockpricemerge.low = row.low
AND stockpricemerge.close = row.close
AND stockpricemerge.volume = row.volume;
END;
END LOOP;
END;
When this function is used in version 9.1.3, it gives the following error:
ERROR: record "row" has no field "open"
LINE 1: ...ume, occurrence ) VALUES ( row.stockid, row.date, row.open ,...
^
QUERY: INSERT INTO stockpricemerge (stockid, date, open, high, low, close,
volume, occurrence ) VALUES ( row.stockid, row.date, row.open , row.high,
row.low, row.close, row.volume, 1 )
CONTEXT: PL/pgSQL function "insert_stockpricemerge1" line 8 at SQL
statement
********** Error **********
ERROR: record "row" has no field "open"
SQL state: 42703
Context: PL/pgSQL function "insert_stockpricemerge1" line 8 at SQL statement
The function works fine when I replace row.open and row.close with an
integer value.
So, my conclusion is that column names "open" and "close" are causing
problems.
Any workaround for this problem ?
thanks,
Mark
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Plpgsql-9-1-3-not-accepting-open-close-as-column-names-tp5657223p5657223.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Devrim GÜNDÜZ | 2012-04-22 20:11:38 | Re: yum repo issue |
Previous Message | Adrian Klaver | 2012-04-22 18:38:57 | Re: Plpgsql 9.1.3 : not accepting "open", "close" as column names |