Using RETURNING with INTO inside pgsql
Not really a question here, I just wanted to share my joy with the
group. I'm loving the new RETURNING clause in PostgreSQL. This is
really cool stuff ...
-------------------- 8< --------------------
CREATE OR REPLACE FUNCTION "public"."testfunc" () RETURNS bigint AS
$body$
DECLARE
my_var BIGINT;
BEGIN
INSERT INTO tryit (col1, col2)
VALUES ('a', 'b')
RETURNING prikey
INTO my_var;
RETURN my_var;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
-------------------- 8< --------------------
I never do anything this trivial inside a stored proc, but the point is
not what I'm doing but rather how it's done. That RETURNING clause
really helps reduce the amount of SQL I have to write. PostgreSQL 8.2.4
rocks!
-- Dante
On 9/21/07, D. Dante Lorenso <dante@lorenso.com> wrote:
Not really a question here, I just wanted to share my joy with the
group. I'm loving the new RETURNING clause in PostgreSQL. This is
really cool stuff ...
I love it too! I have an update for out monitoring software that does
something like:
insert into monitoring table (a,b,c,thredhols) select distinct a,b,c,
50 from bigtable where date > now() - interval '24 hours' returning
a,b,c
and it is so much simpler than how I had to do it before, mostly in app code.
Every time I turn around there's something new in postgresql that
makes me love it that much more.
I owe somebody a whole bunch of pizza...
I have to agree with both of you...
But unfortunately there are still some loose ends... See bug 3596...
http://archives.postgresql.org/pgsql-bugs/2007-09/msg00009.php
But leaving bugs aside, I will have to say "Bravo!" to the development team!
Ciprian Craciun.
P.S.: I forgot to press reppy all...
Show quoted text
On 9/21/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On 9/21/07, D. Dante Lorenso <dante@lorenso.com> wrote:
Not really a question here, I just wanted to share my joy with the
group. I'm loving the new RETURNING clause in PostgreSQL. This is
really cool stuff ...I love it too! I have an update for out monitoring software that does
something like:insert into monitoring table (a,b,c,thredhols) select distinct a,b,c,
50 from bigtable where date > now() - interval '24 hours' returning
a,b,cand it is so much simpler than how I had to do it before, mostly in app code.
Every time I turn around there's something new in postgresql that
makes me love it that much more.I owe somebody a whole bunch of pizza...
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Woah, when did that come around? Talk about sweet syntactic sugar....
On Sep 20, 2007, at 10:12 PM, D. Dante Lorenso wrote:
Show quoted text
I'm loving the new RETURNING clause in PostgreSQL. This is really
cool stuff ...
You blinked again, didn't you? :)
Show quoted text
On 9/21/07, Ben <bench@silentmedia.com> wrote:
Woah, when did that come around? Talk about sweet syntactic sugar....
On Sep 20, 2007, at 10:12 PM, D. Dante Lorenso wrote:
I'm loving the new RETURNING clause in PostgreSQL. This is really
cool stuff ...---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Ben wrote:
Woah, when did that come around? Talk about sweet syntactic sugar....
8.2
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +