oh, ok understood. What will happen for a timestamp field. Let us say c1 is a timestamp column.
sqlstr := 'insert into test(c1, c2) values ('||'\''||COALESCE(rec.c1,'NULL')||'\','
||'\''||rec.c2||'\')';
If this case the query will be insert into test(c1,c2) values ('2004-02-13', 'Hai')
If there is a null value encountered i will return an error for the following query insert into test(c1,c2) values ('NULL', 'Hai') ERROR: Bad timestamp external representation 'NULL'
It's because you can't use quotes with null. Valid query is: insert into test(c1,c2) values (NULL, 'Hai');
Your dynamic query will then look like:
sqlstr := 'insert into test(c1, c2) values (' ||COALESCE('\'' || rec.c1 || '\'','NULL') ...
or more elegant:
sqlstr := 'insert into test(c1, c2) values (' ||COALESCE(quote_literal(rec.c1),'NULL') ...
Regards, Tomasz Myrta
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
