Re: computed values in plpgsql

On Tue, 2009-09-29 at 07:54 -0400, Merlin Moncure wrote:

> you are missing some quotes in there. also, don't use 'values', use
> select. see my example above:
> execute 'insert into foo_something select (''' || new::text || '''::foo).*';
>
> the actual query should look like:
> insert into payments(payment_name, payment_type, when_done, amount)
> select ('(7,FRED,WIDGET,"2009-01-15 14:20:00",14.500)'::payments).*;
>
> merlin

Merlin,
thank you. That appears to work except for one case. If one of the
string literals in the insert happens to have an escaped quote (e.g.
'Joe''s Crabshack') the insert falls over due to quoting.

insert into inquiries(who, when_done, question ) values('FRED',TIMESTAMP '2009-01-16 09:14:00', 'Where''s my money');

LINE 1: ...901 select ('(1,FRED,"2009-01-16 09:14:00","Where's my money...
^
QUERY: insert into inquiries_200901 select ('(1,FRED,"2009-01-16 09:14:00","Where's my money")'::inquiries).*

Does anyone know...
if 8.4 would have the same issue?
is there a non-trivial solution to this that could be implemented in the plpgsql function