Thread: user defined type, plpgsql function and NULL

user defined type, plpgsql function and NULL

From
"Bjoern A. Zeeb"
Date:
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 = endfoobar
--------     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


Re: user defined type, plpgsql function and NULL

From
Tom Lane
Date:
"Bjoern A. Zeeb" <bzeeb-lists@lists.zabbadoz.net> writes:
> Is this correct or is it just a "works like that this time but may
> change at any time in the future"?

The meaning of a NULL for a composite value isn't real well-defined
at the moment.  I tend to agree that "fbt IS NULL" should yield true
in your example, but I think there are/were some implementation reasons
why it doesn't.
        regards, tom lane