Thread: 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.

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@gmail.com

hi,

When using row."open"  and row."close"  the function was working fine.
Many thanks for replying and help.


Mark


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Plpgsql-9-1-3-not-accepting-open-close-as-column-names-tp5657223p5657967.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.