Re: Testing for null record in plpgsql

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(at)myemma(dot)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(at)myemma(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>