From: Erik Jones <erik@myemma.com>
To: pgsql-sql@postgresql.org List <pgsql-sql@postgresql.org>
Subject: Testing for null record in plpgsql
Date: Thu, 10 Apr 2008 23:16:40 -0500
Message-ID: <491AB2F3-6ACF-4D58-A195-9D7A848E479A@myemma.com> (raw)
Ok, let's say I have the following:
CREATE TABLE foo (
val1 integer,
val2 integer
);
CREATE OR REPLACE FUNCTION returns_null_maybe()
RETURNS foo AS $$
DECLARE
res integer;
BEGIN
SELECT INTO res extract('month' from now()::integer % 2;
IF res == 0 THEN
RETURN NULL;
ELSE
RETURN (5,5)::foo;
END IF;
END;
$$ LANGUAGE plpgsql;
Now, let's say I want to call this from another function and test the
result to see if I have a null record (null, null),. I've got the
following working but it feels like there should be something much
simpler but I just can't seem to hit on it. Is this it?
CREATE FUNCTION test_null_rec()
RETURNS boolean AS $$
DECLARE
res boolean;
null_rec foo;
BEGIN
SELECT INTO res * FROM returns_null_maybe();
IF row(res.*) IS DISTINCT FROM row(null_rec.*) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$$ LANGUAGE plpgsql;
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
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: erik@myemma.com
Subject: Re: Testing for null record in plpgsql
In-Reply-To: <491AB2F3-6ACF-4D58-A195-9D7A848E479A@myemma.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