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..
