pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeduser defined type, plpgsql function and NULL
2+ messages / 2 participants
[nested] [flat]
* user defined type, plpgsql function and NULL
@ 2005-09-08 08:09 Bjoern A. Zeeb <bzeeb-lists@lists.zabbadoz.net>
2005-09-13 04:58 ` Re: user defined type, plpgsql function and NULL Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Bjoern A. Zeeb @ 2005-09-08 08:09 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: user defined type, plpgsql function and NULL
2005-09-08 08:09 user defined type, plpgsql function and NULL Bjoern A. Zeeb <bzeeb-lists@lists.zabbadoz.net>
@ 2005-09-13 04:58 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-09-13 04:58 UTC (permalink / raw)
To: Bjoern A. Zeeb <bzeeb-lists@lists.zabbadoz.net>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-09-13 04:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-08 08:09 user defined type, plpgsql function and NULL Bjoern A. Zeeb <bzeeb-lists@lists.zabbadoz.net>
2005-09-13 04:58 ` Tom Lane <tgl@sss.pgh.pa.us>
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