Fwd: DB2-style INS/UPD/DEL RETURNING

On 3/13/06, Lukas Smith <smith(at)pooteeweet(dot)org> wrote:

> I guess you could get the same effect from a transaction. If there is
> much network overhead you could also write a stored procedure. This is
> obviously "more direct". Due to caching I am not sure how much
> performance improvement there is in doing the read/write in one
> statement.

What are you talking about?

IMHO this is only useful in fringe cases, can be implemented efficiently
> with existing syntax and so just adds useless complexity.

Show me an example of how "efficiently" you can get this syntax. Let's see,
I know I could get it using our current syntax too...

SELECT test_id FROM insert_into_table('test_tbl',
array['nextval(\'test_id_seq\')','test']) AS t1(test_id BIGINT);

Where insert_into_table is a general function that takes inputs, builds the
insert statement, executes the insert statement, builds a selection, and
returns the row. Of course, you could have a *custom* function that only
works for the test_tbl, that would make it easy to return the next sequence
id... or wait, you could write the general function to go lookup the table
definition, find out for itself to do the nextval, and do more craziness ad
nauseum.

In the end, how is this more efficient or easy than:

INSERT INTO test_tbl VALUES (nextval('test_id_seq'), 'John Doe') RETURNING
test_id;
OR
SELECT test_id FROM NEW TABLE INSERT INTO test_tbl VALUES
(nextval('test_id_seq'), 'John Doe');

Based on your statement, this should be really easy. It's easy to make
generalized statements, so let's see an example to compare.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324