Re: computed values in plpgsql

2009/9/29 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> On Tue, Sep 29, 2009 at 05:42:37PM +0200, Pavel Stehule wrote:
>> you cannot use double quotes. It's not php.
>
> Normally yes, but *inside* literals you do indeed want double quotes.
>
>
> I think the OP wants to be using quote_literal here.  I.e. instead of:
>
>  execute 'insert into foo_something select (''' || new::text || '''::foo).*';
>
> it wants to be closer to:
>
>  execute 'insert into foo_something select (foo ' || quote_literal(new) || ').*;';
>
> but it's a bit fiddly and I may have got that wrong somewhere else.

I afraid so this technique is very buggy. You need unpacked serialised
record. And the result have to be valid sql literal.

postgres=# create type t as (name varchar, addr varchar);
CREATE TYPE
postgres=# select row('Pavel Stehule','Benesov')::t;
row
---------------------------
("Pavel Stehule",Benesov)
(1 row)

postgres=# select (row('Pavel Stehule','Benesov')::t).*;
name | addr
---------------+---------
Pavel Stehule | Benesov
(1 row)

but you need 'Pavel Stehule','Benesov'

you cannot apply quote literal on two or more columns. I thing, so
this isn't possible now.

Pavel

>
> --
>  Sam  http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>