August 9th, 2013, 03:32 PM
Using Execute(), Format() and NEW
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:
Which throws the error:
INSERT INTO "%s" VALUES (%s);
', TableName, new);
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.
August 13th, 2013, 04:29 PM
You're looking for something more like
'INSERT INTO ' || quote_ident(table_name_var) || ' (f1, f2)'
' VALUES ($1, $2)'
USING NEW.f1, NEW.f2;