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

Testing for null record in plpgsql

From: Erik Jones <erik(at)myemma(dot)com>
To: "pgsql-sql(at)postgresql(dot)org List" <pgsql-sql(at)postgresql(dot)org>
Subject: Testing for null record in plpgsql
Date: 2008-04-11 04:16:40
Message-ID: 491AB2F3-6ACF-4D58-A195-9D7A848E479A@myemma.com (view raw or flat)
Thread:
Lists: pgsql-sql
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®
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




Responses

pgsql-sql by date

Next:From: Pavel StehuleDate: 2008-04-11 05:21:58
Subject: Re: Testing for null record in plpgsql
Previous:From: Alvaro HerreraDate: 2008-04-10 15:21:06
Subject: Re: [HACKERS] [SQL] pl/PgSQL, variable names in NEW

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