[fv967]

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 

 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

[fv967]

When using row."open" and row."close" the function was working fine.


Mark