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@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
>