Thread: Testing for null record in plpgsql
Ok, let's say I have the following:
CREATE TABLE foo (val1 integer,val2 integer
);
CREATE OR REPLACE FUNCTION returns_null_maybe()RETURNS foo AS $$
DECLAREres integer;
BEGINSELECT INTO res extract('month' from now()::integer % 2;IF res == 0 THEN RETURN NULL;ELSE RETURN
(5,5)::foo;ENDIF;
END;
$$ LANGUAGE plpgsql;
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?
CREATE FUNCTION test_null_rec()RETURNS boolean AS $$
DECLAREres boolean;null_rec foo;
BEGINSELECT INTO res * FROM returns_null_maybe();
IF row(res.*) IS DISTINCT FROM row(null_rec.*) THEN RETURN TRUE;ELSE RETURN FALSE;END IF;
END;
$$ LANGUAGE plpgsql;
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
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
Hello
maybe
CREATE OR REPLACE FUNCTION returns_empty_set_maybe()
RETURNS SETOF foo AS $$ res integer; aux foo;BEGIN SELECT INTO res extract('month' from
now()::integer% 2; IF res <> 0 THEN aux = (5,5); RETURN NEXT aux; END IF; RETURN;
END;
$$ LANGUAGE plpgsql;
and then in your function
DECLARE x foo;
BEGIN SELECT INTO x * FROM returns_empty_set_maybe(); RETURN found;
END;
Regards
Pavel Stehule
On 11/04/2008, Erik Jones <erik@myemma.com> wrote:
> Ok, let's say I have the following:
>
> CREATE TABLE foo (
> val1 integer,
> val2 integer
> );
>
> CREATE OR REPLACE FUNCTION returns_null_maybe()
> RETURNS foo AS $$
> DECLARE
> res integer;
> BEGIN
> SELECT INTO res extract('month' from now()::integer % 2;
>
> IF res == 0 THEN
> RETURN NULL;
> ELSE
> RETURN (5,5)::foo;
> END IF;
> END;
> $$ LANGUAGE plpgsql;
>
> 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?
>
> CREATE FUNCTION test_null_rec()
> RETURNS boolean AS $$
> DECLARE
> res boolean;
> null_rec foo;
> BEGIN
> SELECT INTO res * FROM returns_null_maybe();
>
> IF row(res.*) IS DISTINCT FROM row(null_rec.*) THEN
> RETURN TRUE;
> ELSE
> RETURN FALSE;
> END IF;
> END;
> $$ LANGUAGE plpgsql;
>
> Erik Jones
>
> DBA | Emma(R)
> 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
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/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