Search Postgresql Archives

Re: computed values in plpgsql

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Sep 29, 2009 at 10:49 AM, Reid Thompson <reid.thompson@xxxxxxxx> wrote:
> 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

dollar quoting can get you pretty far (bytea values can still be a problem):
create table foo(id int, a text, b text);

insert into foo values (1,'ab''cd', 'ab"cd');

create or replace function test_insert() returns void as
$$
declare
 r text;
begin
 select foo::text from foo limit 1 into r;

 execute 'insert into foo select ($q$' || r || '$q$::foo).*';
end;
$$ language plpgsql;

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux