Re: Testing for null record in plpgsql

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=20
distinguish a completely null record from one that isn't.  I can see=20=20
by both of your responses that by incor****ating that in a dummy=20=20
example I inadvertently added misleading context.  In my actual
code,=20=20
nothing returns boolean, I just used that to show the crux of the=20=20
specific syntax/semantics issue I was having.  What I've actually
got=20=20
are foo_ins_func() and foo_ins_trig() where foo is a table with both=20=20
functions being generated dynamically by userland scripts.=20=20=20
foo_ins_trig() is a BEFORE INSERT trigger function that calls=20=20
foo_ins_func() (this layering allows me to use foo_ins_func() both=20=20
directly and in the trigger) so, Pavel: I can't just return the record=20=
=20
that gets returned from foo_ins_func as if it's completely null that=20=20
causes an error -- trigger functions need to return NULL not (NULL,=20=20
NULL), and Craig: obviously I can't return a simple boolean from a=20=20
trigger function.

Here are the dynamically generated functions I've been using for=20=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=
=20
|| '_' ||
                          name_parts[upper_dim] || ' (id,val,val_ts)=20=20
VALUES (' || quote_nullable(rec.id) || ',' ||
quote_nullable(rec.val)=20=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=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=20
automation scripts I'm working on.  Once I've got it rounded out=20=20
(right now it only sup****ts timestamps but I doubt swapping in integer=20=
=20
or dates will be difficult) and a test suite written I'll probably=20=20
throw it up on github since people often ask how to do this kind of=20=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=20
>> the result to see if I have a null record (null, null),.  I've
got=20=20
>> the following working but it feels like there should be something=20=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=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=20
> value and returning NULL in its place. It seems like an odd thing to=20=
=20
> do. Still, I'm sure you have your reasons and they probably make=20=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 incor****ating it into the containing query as=20=
=20
> a subquery I don't see much to be done. I'm still curious about
the=20=20
> purpose of using null values like this is, though.
>
> --
> Craig Ringer
>
> --=20
> Sent via pgsql-sql mailing list (pgsql-sql@[EMAIL PROTECTED]
)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

Erik Jones

DBA | Emma=AE
erik@[EMAIL PROTECTED]
 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




--=20
Sent via pgsql-sql mailing list (pgsql-sql@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql