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

Here are the dynamically generated functions I've been using for
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 := string_to_array(partition, '_');
upper_dim := array_upper(name_parts, 1);
IF rec.val_ts >= name_parts[upper_dim-1]::timestamp
AND rec.val_ts < name_parts[upper_dim]::timestamp THEN
ins_sql := 'INSERT INTO foo_' || name_parts[upper_dim-1]
|| '_' ||
name_parts[upper_dim] || ' (id,val,val_ts)
VALUES (' || quote_nullable(rec.id) || ',' || quote_nullable(rec.val)
|| ',' || quote_nullable(rec.val_ts) || ');';
EXECUTE ins_sql;
RETURN NULL;
END IF;
END LOOP;
RAISE WARNING 'No partiion created for foo to hold timestamp
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
automation scripts I'm working on. Once I've got it rounded out
(right now it only supports timestamps but I doubt swapping in integer
or dates will be difficult) and a test suite written I'll probably
throw it up on github since people often ask how to do this kind of
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
>> 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?
> I'm assuming that returns_null_maybe() is a dummy to show general
> 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
> value and returning NULL in its place. It seems like an odd thing to
> do. Still, I'm sure you have your reasons and they probably make
> 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
> a subquery I don't see much to be done. I'm still curious about the
> purpose of using null values like this is, though.
>
> --
> Craig Ringer
>
> --
> 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

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