user defined type, plpgsql function and NULL

From: "Bjoern A(dot) Zeeb" <bzeeb-lists(at)lists(dot)zabbadoz(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: user defined type, plpgsql function and NULL
Date: 2005-09-08 08:09:13
Message-ID: dforj9$9cg$1@news.germany.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Greg Sabino Mullane 2005-09-08 13:45:10 Re: uuid type for postgres
Previous Message Greg Stark 2005-09-08 01:13:01 Re: uuid type (moved from HACKERS)