pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTesting for null record in plpgsql
4+ messages / 3 participants
[nested] [flat]
* Testing for null record in plpgsql
@ 2008-04-11 04:16 Erik Jones <erik@myemma.com>
0 siblings, 2 replies; 4+ messages in thread
From: Erik Jones @ 2008-04-11 04:16 UTC (permalink / raw)
To: pgsql-sql
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®
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Testing for null record in plpgsql
@ 2008-04-11 05:21 Pavel Stehule <pavel.stehule@gmail.com>
parent: Erik Jones <erik@myemma.com>
1 sibling, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2008-04-11 05:21 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Testing for null record in plpgsql
@ 2008-04-11 07:40 Craig Ringer <craig@postnewspapers.com.au>
parent: Erik Jones <erik@myemma.com>
1 sibling, 1 reply; 4+ messages in thread
From: Craig Ringer @ 2008-04-11 07:40 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Testing for null record in plpgsql
@ 2008-04-11 14:35 Erik Jones <erik@myemma.com>
parent: Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 4+ messages in thread
From: Erik Jones @ 2008-04-11 14:35 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; Pavel Stehule <pavel.stehule@gmail.com>; +Cc: 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 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-04-11 14:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-11 04:16 Testing for null record in plpgsql Erik Jones <erik@myemma.com>
2008-04-11 05:21 ` Pavel Stehule <pavel.stehule@gmail.com>
2008-04-11 07:40 ` Craig Ringer <craig@postnewspapers.com.au>
2008-04-11 14:35 ` Erik Jones <erik@myemma.com>
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