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

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0

    Using Execute(), Format() and NEW


    Hi,

    In a trigger function, I need to insert the record NEW in a table. The name of the table is stored in a variable, so unless I am mistaken, I must use the EXECUTE and format functions.

    This is my code:

    Code:
    EXECUTE format('
    INSERT INTO "%s" VALUES (%s);
    ', TableName, new);
    Which throws the error:

    ERROR: zero-length delimited identifier at or near """"
    LINE 2: ...7,0,2008-07-02,151,0037," ",1,1994-06-22,1,190,"","\\\\Pr...

    This error raises because 'NEW' returns a string with values enclosed in a double-quote instead of single quote.

    Running the query as a static statement works, however it is not a solution because I do not know the table name in advance.

    How do I insert the record NEW into a table which name is not static (ie stored in a variable)?

    I haven't found any workaround for this. Any help would be greatly appreciated.

    Thank you!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    You're looking for something more like
    sql Code:
    EXECUTE
        'INSERT INTO ' || quote_ident(table_name_var) || ' (f1, f2)'
        '    VALUES ($1, $2)'
        USING NEW.f1, NEW.f2;

IMN logo majestic logo threadwatch logo seochat tools logo