Composite types and NULL within PL/pgSQL

Поиск
Список
Период
Сортировка
От David J N Begley
Тема Composite types and NULL within PL/pgSQL
Дата
Msg-id Pine.LNX.4.61.0605070051330.18931@viper.uws.edu.au
обсуждение исходный текст
Ответы Re: Composite types and NULL within PL/pgSQL  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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..

В списке pgsql-general по дате отправления:

Предыдущее
От: Volkan YAZICI
Дата:
Сообщение: Re: intarray internals
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Composite types and NULL within PL/pgSQL