Composite types and NULL within PL/pgSQL

From: David J N Begley <d(dot)begley(at)uws(dot)edu(dot)au>
To: pgsql-general(at)postgresql(dot)org
Subject: Composite types and NULL within PL/pgSQL
Date: 2006-05-06 15:11:42
Message-ID: Pine.LNX.4.61.0605070051330.18931@viper.uws.edu.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2006-05-06 15:28:44 Re: Composite types and NULL within PL/pgSQL
Previous Message Volkan YAZICI 2006-05-06 14:38:24 Re: intarray internals