Re: Plpgsql 9.1.3 : not accepting "open", "close" as column names

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(dot)klaver(at)gmail(dot)com