From: Pavel Stehule <pavel.stehule@gmail.com>
To: Erik Jones <erik@myemma.com>
Cc: pgsql-sql@postgresql.org List <pgsql-sql@postgresql.org>
Subject: Re: Testing for null record in plpgsql
Date: Fri, 11 Apr 2008 07:21:58 +0200
Message-ID: <162867790804102221w5cb2ca8uf9faad037f1a789e@mail.gmail.com> (raw)
In-Reply-To: <491AB2F3-6ACF-4D58-A195-9D7A848E479A@myemma.com>
References: <491AB2F3-6ACF-4D58-A195-9D7A848E479A@myemma.com>
Hello
maybe
CREATE OR REPLACE FUNCTION returns_empty_set_maybe()
RETURNS SETOF foo AS $$
res integer;
aux foo;
BEGIN
SELECT INTO res extract('month' from now()::integer % 2;
IF res <> 0 THEN
aux = (5,5);
RETURN NEXT aux;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
and then in your function
DECLARE x foo;
BEGIN
SELECT INTO x * FROM returns_empty_set_maybe();
RETURN found;
END;
Regards
Pavel Stehule
On 11/04/2008, Erik Jones <erik@myemma.com> wrote:
> 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(R)
> 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
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
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: pavel.stehule@gmail.com, erik@myemma.com
Subject: Re: Testing for null record in plpgsql
In-Reply-To: <162867790804102221w5cb2ca8uf9faad037f1a789e@mail.gmail.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