Re: [SQL] Testing for null record in plpgsql
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
Re: [SQL] Testing for null record in plpgsql
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 <[EMAIL PROTECTED]> 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)
> [EMAIL PROTECTED]
> 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
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Erik Jones
DBA | Emma®
[EMAIL PROTECTED]
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
[SQL] Get the last inserted id
Hi all, I was wondering if postgres can return the last ID inserted for a table which the primary key is autoincremented. I need to retrieve this ID in my code to continue processing on that inserted row. Thanks to all
Re: [SQL] Get the last inserted id
On Fri, Apr 11, 2008 at 11:23 AM, Nacef LABIDI <[EMAIL PROTECTED]> wrote: > Hi all, > > I was wondering if postgres can return the last ID inserted for a table > which the primary key is autoincremented. I need to retrieve this ID in my > code to continue processing on that inserted row. smarlowe=# create table test (a serial primary key, b text); NOTICE: CREATE TABLE will create implicit sequence "test_a_seq" for serial column "test.a" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" CREATE TABLE smarlowe=# insert into test (a,b) values (DEFAULT,'test') returning a; a --- 1 (1 row) INSERT 0 1 Note that there are other ways of doing it, but I really like the returning clause. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Get the last inserted id
Nice idea but how can I use this in a frontend that I am developing using Delphi and ADO objects ? On Fri, Apr 11, 2008 at 7:41 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Fri, Apr 11, 2008 at 11:23 AM, Nacef LABIDI <[EMAIL PROTECTED]> wrote: > > Hi all, > > > > I was wondering if postgres can return the last ID inserted for a table > > which the primary key is autoincremented. I need to retrieve this ID in > my > > code to continue processing on that inserted row. > > smarlowe=# create table test (a serial primary key, b text); > NOTICE: CREATE TABLE will create implicit sequence "test_a_seq" for > serial column "test.a" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "test_pkey" for table "test" > CREATE TABLE > smarlowe=# insert into test (a,b) values (DEFAULT,'test') returning a; > a > --- > 1 > (1 row) > > INSERT 0 1 > > Note that there are other ways of doing it, but I really like the > returning clause. >
Re: [SQL] Get the last inserted id
am Fri, dem 11.04.2008, um 19:23:04 +0200 mailte Nacef LABIDI folgendes: > Hi all, > > I was wondering if postgres can return the last ID inserted for a table which > the primary key is autoincremented. I need to retrieve this ID in my code to > continue processing on that inserted row. Either with RETURNING: http://www.postgresql.org/docs/current/static/sql-insert.html or with currval(): http://www.postgresql.org/docs/current/static/functions-sequence.html HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Get the last inserted id
On Fri, Apr 11, 2008 at 07:23:04PM +0200, Nacef LABIDI wrote: > Hi all, > > I was wondering if postgres can return the last ID inserted for a table > which the primary key is autoincremented. I need to retrieve this ID in my > code to continue processing on that inserted row. Use select currval() on the sequence name. A -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
