Message-ID: <162867790804102221w5cb2ca8uf9faad037f1a789e@mail.gmail.com>
Date: Fri, 11 Apr 2008 07:21:58 +0200
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Erik Jones" <erik@myemma.com>
Subject: Re: Testing for null record in plpgsql
Cc: "pgsql-sql@postgresql.org List" <pgsql-sql@postgresql.org>
In-Reply-To: <491AB2F3-6ACF-4D58-A195-9D7A848E479A@myemma.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
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
>