From: "Bjoern A. Zeeb" <bzeeb-lists@lists.zabbadoz.net>
Subject: user defined type, plpgsql function and NULL
Date: Thu, 8 Sep 2005 08:09:13 +0000 (UTC)
Organization: Zabbadoz.NeT
Lines: 64
Message-ID: <dforj9$9cg$1@news.germany.com>
User-Agent: tin/1.7.7-20041215 ("Scalpay") (UNIX) (FreeBSD/5.3-BETA7
 (sparc64))
To: pgsql-sql@postgresql.org

Hi,
   
let's say one has an user defined data type
   
CREATE TYPE foobar_t AS ( va varchar(25), vb varchar(4), vc varchar(20), ia integer, ib integer );
   
and a stored procedure in plgpsql (stripped and sample only):
   
CREATE OR REPLACE FUNCTION foobar(int, foobar_t, int, varchar) RETURNS INT AS'
DECLARE
        ia              ALIAS FOR $1;
        fbt             ALIAS FOR $2;
        ib              ALIAS FOR $3;
        vc              ALIAS FOR $4;
BEGIN
        ...
        IF fbt IS NULL THEN
                RAISE NOTICE ''fbt IS NULL;'';
        ELSE
                RAISE NOTICE ''fbt IS NOT NULL... '';
                IF fbt.va IS NULL THEN
                        RAISE NOTICE ''fbt.va IS NULL;'';
                ELSE
                        RAISE NOTICE ''fbt.va = %'', fbt.va;
                END IF;
                ...
        END IF;
        ...
   
        RETURN 0;
END'
        LANGUAGE plpgsql;
   
   
If one does a
   
SELECT foobar(1, NULL, 2, 'end');
NOTICE:  ia = 1
NOTICE:  fbt IS NOT NULL...
NOTICE:  fbt.va IS NULL;
NOTICE:  fbt.vb IS NULL;
NOTICE:  fbt.vc IS NULL;
NOTICE:  fbt.ia IS NULL;
NOTICE:  fbt.ib IS NULL;
NOTICE:  ib = 2
NOTICE:  vc = end
 foobar
--------
      0
(1 row)
   
   
Note the second argument foobar_t is given as NULL
but $2 IS NOT NULL.
   
I cannot find anything about this in the docs but I asume
that the single NULL will implicitly set all attributes to NULL?
Is this correct or is it just a "works like that this time but may
change at any time in the future"?
   
   
-- 
Greetings
Bjoern A. Zeeb