Thread: re-using RETURNING

Hi,

just to be sure, it is still (8.4) not possible to use RETURNING within an
other INSERT?

To show what i mean:

test=*# insert into foo2 (id1, id2) (insert into foo(n) values ('a'),('b'),('c') returning currval('some_sequence'),
id);
ERROR:  syntax error at or near "insert"
LINE 1: insert into foo2 (id1, id2) (insert into foo(n) values ('a')...

I know, it is intended in 8.5 with the 'WITH' - clause, but not yet in 8.4, right?

(and there are no other RDBMS which can do that?)

Thanks, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

2009/11/12 A. Kretschmer <andreas.kretschmer@schollglas.com>:
> Hi,
>
> just to be sure, it is still (8.4) not possible to use RETURNING within an
> other INSERT?
>
> To show what i mean:
>
> test=*# insert into foo2 (id1, id2) (insert into foo(n) values ('a'),('b'),('c') returning currval('some_sequence'),
id);
> ERROR:  syntax error at or near "insert"
> LINE 1: insert into foo2 (id1, id2) (insert into foo(n) values ('a')...
>
> I know, it is intended in 8.5 with the 'WITH' - clause, but not yet in 8.4, right?
>
> (and there are no other RDBMS which can do that?)

Well there is a little trik in actual versions...

use these options:
\a
\t
\o /tmp/archivo.csv

then
DELETE FROM tabla WHERE entero =13 RETURNING *;

(remember that you can throught this query from shell command line, with
psql options)

(next step, delete the last line of the file archivo.csv: DELETE 9890)

create a clon - void table (this will be your log table, if you have
already created this
, avoid this step):
postgres=# CREATE TABLE tabla_2 AS SELECT * FROM tabla WHERE 1=0;
SELECT

Then you can go with this
postgres=# COPY tabla_2 FROM '/tmp/returnes.csv' DELIMITER '|';
COPY 19780
postgres=# SELECT * from tabla_2 limit 1;
 entero
--------
     13
(1 row)


Obviusly, you can automatize these steps in a shell script.

Hope you enjoy it.


--
              Emanuel Calvo Franco
             DBA at:  www.siu.edu.ar
        www.emanuelcalvofranco.com.ar

On 12/11/2009 9:41 PM, A. Kretschmer wrote:
> Hi,
>
> just to be sure, it is still (8.4) not possible to use RETURNING within an
> other INSERT?

Not directly, now. However, if I recall correctly in 8.4 you *CAN* use a
... RETURNING statement within an SQL function, and use the results of
that in another query.

--
Craig Ringer

A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:

> Hi,
>
> just to be sure, it is still (8.4) not possible to use RETURNING within an
> other INSERT?

Thx for all replies. It is not a really problem, i will write a
benchmark to compare the new writeable CTE (in 8.5 alpha) with the old
style (8.4). That's all ;-)

And yes, i will publish the result, of course.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Andreas Kretschmer <akretschmer@spamfence.net> wrote:

> A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
>
> > Hi,
> >
> > just to be sure, it is still (8.4) not possible to use RETURNING within an
> > other INSERT?
>
> Thx for all replies. It is not a really problem, i will write a
> benchmark to compare the new writeable CTE (in 8.5 alpha) with the old
> style (8.4). That's all ;-)
>
> And yes, i will publish the result, of course.

http://akretschmer.blogspot.com/2009/11/writeable-cte-short-performance-test.html


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On 2009-11-12, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
> Hi,
>
> just to be sure, it is still (8.4) not possible to use RETURNING within an
> other INSERT?

not in pure SQL, but it should be possible in PLPGSQL etc.