Cc: "pgsql-sql@postgresql.org List" <pgsql-sql@postgresql.org>
Message-Id: <F0882F43-51B8-4F2B-A478-F82BD48BDC67@myemma.com>
From: Erik Jones <erik@myemma.com>
To: Craig Ringer <craig@postnewspapers.com.au>,
	Pavel Stehule <pavel.stehule@gmail.com>
In-Reply-To: <47FF1605.8050301@postnewspapers.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Apple Message framework v919.2)
Subject: Re: Testing for null record in plpgsql
Date: Fri, 11 Apr 2008 09:35:28 -0500
References: <491AB2F3-6ACF-4D58-A195-9D7A848E479A@myemma.com>
	<47FF1605.8050301@postnewspapers.com.au>

Pavel & Craig, I'm replying to both of you to save some time :)

All I was really trying to demonstrate was the need to be able to =20
distinguish a completely null record from one that isn't.  I can see =20
by both of your responses that by incorporating that in a dummy =20
example I inadvertently added misleading context.  In my actual code, =20=

nothing returns boolean, I just used that to show the crux of the =20
specific syntax/semantics issue I was having.  What I've actually got =20=

are foo_ins_func() and foo_ins_trig() where foo is a table with both =20
functions being generated dynamically by userland scripts.  =20
foo_ins_trig() is a BEFORE INSERT trigger function that calls =20
foo_ins_func() (this layering allows me to use foo_ins_func() both =20
directly and in the trigger) so, Pavel: I can't just return the record =20=

that gets returned from foo_ins_func as if it's completely null that =20
causes an error -- trigger functions need to return NULL not (NULL, =20
NULL), and Craig: obviously I can't return a simple boolean from a =20
trigger function.

Here are the dynamically generated functions I've been using for =20
testing along with the table def:

CREATE TABLE foo (
	id serial primary key,
	val integer,
	val_ts timestamp without time zone not null
);

CREATE OR REPLACE FUNCTION foo_ins_func(rec foo)
	RETURNS foo AS $$
DECLARE
      partition varchar;
      name_parts varchar[];
      upper_dim integer;
      ins_sql varchar;
  BEGIN
      FOR partition IN
          SELECT relname
          FROM pg_class
          WHERE relname ~ ('^foo_[0-9]{8}_[0-9]{8}$')
      LOOP
          name_parts :=3D string_to_array(partition, '_');
          upper_dim :=3D array_upper(name_parts, 1);
          IF rec.val_ts >=3D name_parts[upper_dim-1]::timestamp
                  AND rec.val_ts < name_parts[upper_dim]::timestamp THEN
              ins_sql :=3D 'INSERT INTO foo_' || name_parts[upper_dim-1] =
=20
|| '_' ||
                          name_parts[upper_dim] || ' (id,val,val_ts) =20
VALUES (' || quote_nullable(rec.id) || ',' || quote_nullable(rec.val) =20=

|| ',' || quote_nullable(rec.val_ts) || ');';
              EXECUTE ins_sql;
              RETURN NULL;
          END IF;
      END LOOP;
      RAISE WARNING 'No partiion created for foo to hold timestamp =20
value %, leaving data in parent table.', rec.val_ts;
      RETURN rec;
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION foo_ins_trig()
	RETURNS trigger AS $$
DECLARE
      res foo;
      null_rec foo;
  BEGIN
      SELECT INTO res * FROM foo_ins_func(NEW) as g;
      IF row(res.*) IS DISTINCT FROM row(null_rec.*) THEN
          RETURN NEW;
      END IF;
      RETURN NULL;
  END;
$$ LANGUAGE plpgsql;

Fwiw, this is a piece of some range based table partitioning =20
automation scripts I'm working on.  Once I've got it rounded out =20
(right now it only supports timestamps but I doubt swapping in integer =20=

or dates will be difficult) and a test suite written I'll probably =20
throw it up on github since people often ask how to do this kind of =20
thing.


On Apr 11, 2008, at 2:40 AM, Craig Ringer wrote:
> Erik Jones wrote:
>> Now, let's say I want to call this from another function and test =20
>> the result to see if I have a null record (null, null),.  I've got =20=

>> the following working but it feels like there should be something =20
>> much simpler but I just can't seem to hit on it.  Is this it?
> I'm assuming that returns_null_maybe() is  a dummy to show general =20
> behavior. I can't imagine why you'd ever want to do what it's doing.
>
> In general I'm suspicious of code that's testing for a real, known =20
> value and returning NULL in its place. It seems like an odd thing to =20=

> do. Still, I'm sure you have your reasons and they probably make =20
> sense in the real application rather than the simplified example.
>
> You can tidy test_null_rec a little by just using:
>
> RETURN row(res.*) IS DISTINCT FROM row(null_rec.*);
>
> but otherwise, without incorporating it into the containing query as =20=

> a subquery I don't see much to be done. I'm still curious about the =20=

> purpose of using null values like this is, though.
>
> --
> Craig Ringer
>
> --=20
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

Erik Jones

DBA | Emma=AE
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