#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2010
    Posts
    7
    Rep Power
    0

    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


    PHP Code:
    CREATE OR REPLACE FUNCTION insert_stockpricemerge1(startdate character varyingenddate character varying)
      
    RETURNS void AS
    $BODY$
      DECLARE
        
    row RECORD
    BEGIN
        
    FOR row IN SELECT stockiddateopenhighlowclosevolume FROM stockpriceretrieve AS 

     LOOP

          BEGIN
            INSERT INTO stockpricemerge 
    (stockiddateopenhighlowclosevolumeoccurrence VALUES row.stockidrow.daterow.open row.highrow.lowrow.closerow.volume);
            
    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:

    PHP Code:
    ERROR:  record "row" has no field "open"
    LINE 1: ...umeoccurrence VALUES row.stockidrow.daterow.open ,...
                                                                 ^
    QUERY:  INSERT INTO stockpricemerge (stockiddateopenhighlowclosevolumeoccurrence VALUES row.stockidrow.daterow.open row.highrow.lowrow.closerow.volume)
    CONTEXT:  PL/pgSQL function "insert_stockpricemerge1" line 8 at SQL statement

    ********** Error **********

    ERRORrecord "row" has no field "open"
    SQL state42703
    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
    Last edited by fv967; April 22nd, 2012 at 04:43 AM. Reason: better explaining text
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2010
    Posts
    7
    Rep Power
    0

    solution found


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


    Mark

IMN logo majestic logo threadwatch logo seochat tools logo