Обсуждение: Composite types and NULL within PL/pgSQL

Folks, I'm having a little trouble with the behaviour of composite types and
NULL - specifically, not being able to assign NULL to such a type within
PL/pgSQL.  The work-around at present is to manually check all fields in the
composite type - hardly efficient when compared to "IS NULL" for the whole
type.

Firstly I wanted to determine whether or not PostgreSQL (8.1.3) saw any
difference between NULL and all-fields-are-NULL for a composite type:

david=# create type iprange as ( lo inet, hi inet );
CREATE TYPE
david=# create table t ( a varchar, b iprange );
CREATE TABLE
david=# insert into t values ( 'first', null );
INSERT 0 1
david=# insert into t values ( 'second', row(null,null) );
INSERT 0 1
david=# select * from t;
   a    |  b
--------+-----
 first  |
 second | (,)
(2 rows)

david=# select * from t where b is null;
   a   | b
-------+---
 first |
(1 row)

Okay, so they are different.  Yet, if within PL/pgSQL I try to assign NULL to
a composite type:

david=# create function tfn( in cidr, out bool, out iprange ) as $$
david$# BEGIN
david$#   IF $1 = '10/8' THEN
david$#     $2 := TRUE;
david$#     $3 := ROW( INET('10.0.0.1'), INET('10.1.0.10') );
david$#   ELSE
david$#     $2 := FALSE;
david$#     $3 := NULL;
david$#   END IF;
david$#   RETURN;
david$# END;
david$# $$ language plpgsql;
CREATE FUNCTION
david=# select tfn('10/8');
            tfn
----------------------------
 (t,"(10.0.0.1,10.1.0.10)")
(1 row)

david=# select tfn('192.168/16');
ERROR:  cannot assign non-composite value to a row variable
CONTEXT:  PL/pgSQL function "tfn" line 7 at assignment

Line 7 refers to "$3 := NULL".  As demonstrated above I can't replace NULL
with ROW(NULL,NULL) as that is not treated the same as NULL - but never mind,
the variables all default to NULL... right?  If I comment-out just that one
line and try again:

david=# select tfn('192.168/16');
    tfn
-----------
 (f,"(,)")
(1 row)

david=# select * from tfn('192.168/16');
 column1 | column2
---------+---------
 f       | (,)
(1 row)

david=# select column2, column2 is null from tfn('192.168/16');
 column2 | ?column?
---------+----------
 (,)     | f
(1 row)

david=# select * from tfn('192.168/16') where column2 is null;
 column1 | column2
-------------------
(0 rows)

Err - no.  By default the composite type returns the all-fields-are-NULL
equivalent instead of just plain NULL.

So... why can I assign NULL to a composite type column in a table but not to a
composite type variable in PL/pgSQL?  Is there any way to force that "out"
composite type variable to be NULL?

Thanks..

David J N Begley <d.begley@uws.edu.au> writes:
> So... why can I assign NULL to a composite type column in a table but not to a
> composite type variable in PL/pgSQL?  Is there any way to force that "out"
> composite type variable to be NULL?

In general the distinction between a true null composite value and a
row-of-nulls is not made very cleanly in PG.  I'd be inclined to
deprecate the former --- you cannot enter such a thing as a row of a
table, for example, and that is the basic property that any rowtype
ought to have no?

In short, don't do that.

            regards, tom lane

On Sat, 6 May 2006, Tom Lane wrote:

> In general the distinction between a true null composite value and a
> row-of-nulls is not made very cleanly in PG.  I'd be inclined to
> deprecate the former --- you cannot enter such a thing as a row of a
> table, for example, and that is the basic property that any rowtype
> ought to have no?

True - though obviously composite types are not always used as whole rows as
in the first example/test from my original message (also in the PostgreSQL
documentation describing composite types);  in that case the composite type
was a single column and could indeed accept a true null.

> In short, don't do that.

Could section 8.11 of the docs be updated to include such a warning?

Tom Lane <tgl@sss.pgh.pa.us> writes:

> you cannot enter such a thing as a row of a table, for example, and that is
> the basic property that any rowtype ought to have no?

Well that logic would apply equally to any SQL datatype. You can't perform
arithmetic on NULL and that's the basic property any integer should have after
all.


--
greg