On 04/22/2012 02:34 AM, fv967 wrote:
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 ?
This is due to changes in 9.0 that tightened variable naming rules.
http://www.postgresql.org/docs/9.1/interactive/release-9-0.html
E.12.2.5. PL/pgSQL
PL/pgSQL now throws an error if a variable name conflicts with a column
name used in a query (Tom Lane)
The former behavior was to bind ambiguous names to PL/pgSQL variables in
preference to query columns, which often resulted in surprising
misbehavior. Throwing an error allows easy detection of ambiguous
situations. Although it's recommended that functions encountering this
type of error be modified to remove the conflict, the old behavior can
be restored if necessary via the configuration parameter
plpgsql.variable_conflict, or via the per-function option
#variable_conflict.
PL/pgSQL no longer allows variable names that match certain SQL reserved
words (Tom Lane)
This is a consequence of aligning the PL/pgSQL parser to match the core
SQL parser more closely. If necessary, variable names can be
double-quoted to avoid this restriction.
My suggestion would be to try double quoting.
thanks,
Mark
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
