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

On Tue, Dec 8, 2009 at 5:43 AM, Teemu Juntunen
<teemu.juntunen@e-ngine.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