From: Erik Jones <erik@myemma.com>
To: Craig Ringer <craig@postnewspapers.com.au>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-sql@postgresql.org List <pgsql-sql@postgresql.org>
Subject: Re: Testing for null record in plpgsql
Date: Fri, 11 Apr 2008 09:35:28 -0500
Message-ID: <F0882F43-51B8-4F2B-A478-F82BD48BDC67@myemma.com> (raw)
In-Reply-To: <47FF1605.8050301@postnewspapers.com.au>
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
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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
Erik Jones
DBA | Emma®
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@myemma.com, craig@postnewspapers.com.au, pavel.stehule@gmail.com
Subject: Re: Testing for null record in plpgsql
In-Reply-To: <F0882F43-51B8-4F2B-A478-F82BD48BDC67@myemma.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox