Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Unanswered: 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

  2. #2
    Join Date
    May 2008
    Posts
    277
    "open" and "close" were always key words; that they worked in 8.4. was probably just luck. This may help: PostgreSQL: Documentation: Manuals: SQL Key Words

    My first suggestion would be to rename the columns to something more explicit: "open_price" and "close_price". If you really can't change the column names, then enclosing the names in double quotes should fix it.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •