Re: Testing for null record in plpgsql - Mailing list pgsql-sql

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
partitionIN         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;
ENDLOOP;     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
DISTINCTFROM 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