Skip site navigation (1) Skip section navigation (2)

Re: Testing for null record in plpgsql

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Erik Jones" <erik(at)myemma(dot)com>
Cc: "pgsql-sql(at)postgresql(dot)org List" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Testing for null record in plpgsql
Date: 2008-04-11 05:21:58
Message-ID: 162867790804102221w5cb2ca8uf9faad037f1a789e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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
>

In response to

pgsql-sql by date

Next:From: Craig RingerDate: 2008-04-11 07:40:53
Subject: Re: Testing for null record in plpgsql
Previous:From: Erik JonesDate: 2008-04-11 04:16:40
Subject: Testing for null record in plpgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group