Re: SELECT ROW(t.*) FROM table t and nulls with textual representation?

On Tue, Dec 8, 2009 at 5:43 AM, Teemu Juntunen
<teemu(dot)juntunen(at)e-ngine(dot)fi> wrote:
> Hello,
>
> is there any possibility to get null-values as text 'null' from
> dynamic select expression like
>
> SELECT ROW(t.*) FROM table t
>
> Normally you get
>
> (t1,t2,t3,,t5,,,)
>
> How can you get
>
> (t1,t2,t3,null,t5,null,null,null)
>
> I would like to use the result with dynamic update expression and update
> doesn't like of empty values, so
>
> UPDATE SET (t1,t2,t3,t4,t5,t6,t7,t8) = (t1,t2,t3,,t5,,,)

It can't be done. The problem is that the update statement has a
funky syntax which is not composite type friendly. What I would
personally like to be table to do is:
update foo set foo = (x,y,z)::foo where...
then you could do:
update foo set foo = '(a,b,)'::foo where...

Couple of posssible workarounds:
*) If you are willing to put the fields you are updating as a block
into a composite type, then you could update them as a block:
create table bar as (...)
create table foo (b bar, ...)
update foo set bar = ()::bar where...;

This isn't a general solution obviously.

*) convert your update to insert+delete:
delete from foo where...
insert into foo select '(a,b,)'::foo;

watch that race condition!

merlin