From: Bjoern A. Zeeb <bzeeb-lists@lists.zabbadoz.net>
To: pgsql-sql@postgresql.org
Subject: user defined type, plpgsql function and NULL
Date: Thu, 8 Sep 2005 08:09:13 +0000 (UTC)
Message-ID: <dforj9$9cg$1@news.germany.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bzeeb-lists@lists.zabbadoz.net
Subject: Re: user defined type, plpgsql function and NULL
In-Reply-To: <dforj9$9cg$1@news.germany.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox