Обсуждение: 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... 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,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... > > ---------------------------(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: > I'm loving the new RETURNING clause in PostgreSQL. This is really > cool stuff ...
You blinked again, didn't you? :) 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. +