Re: comparing NEW and OLD (any good this way?)

On Wed, Aug 12, 2009 at 10:57:54PM +0200, Daniel Verite wrote:
> It seems to me that there is something special with rows: in tables, the
> values of columns may be null or not, but at the level of the row, there is
> no information that would say: this row itself as an object is null.

Hum, there seem to be lots of different things happening here--lets try
and untangle them a bit. I would say that the following returns a null
value of type row (actually a pair of integers):

SELECT b
FROM (SELECT 1) a
LEFT JOIN (SELECT 1,2) b(b1,b2) ON FALSE;

It currently gets serialized as '\N' in the output of psql for me, but
I'd have no problem if it appeared as '(,)'. Both of these seem like
valid representations of a null row to me.

In other discussions about similar issues I've said that the expression:

ROW(NULL,NULL) IS DISTINCT FROM NULL

should evaluate to FALSE. I still think this is correct and generally
useful behavior.

> Anyway, let's try to assign null to a row variable (with 8.4.0):
>
> CREATE TABLE our_table(i int);
>
> CREATE FUNCTION test() returns void as $$
> declare
> r our_table;
> begin
> r:=null;
> end;
> $$ LANGUAGE plpgsql;
>
> SELECT test() yields:
> ERROR: cannot assign non-composite value to a row variable
> CONTEXT: PL/pgSQL function "test" line 4 at assignment

This just looks like PG missing a feature. plpgsql has much less user
and developer time spent on it, so I'd expect to find more strangeness
in darker corners like this.

> As a follow-up to the comparison between rows and arrays, note that if we'd
> make r an int[], there would be no error.

OK, maybe people just do this more often and hence there's been a reason
to make it work.

> However, I agree that if we consider that a row is a composite type, then
> there is a problem because we sure can insert NULL into a column that is of a
> composite type. So the "row cannot be null" line of reasoning holds only so
> far as you don't stuff rows into columns :)

When you say "columns", do you mean the value associated with a
particular attribute in a particular row of a particular table? Surely
this is a normal value and just because it happens to be stored in a
table it shouldn't be any different from any other value anywhere else
in PG.

--
Sam http://samason.me.uk/