From: Rob Wultsch (wultschgmail.com)
Date: Wed Oct 15 2008 - 15:37:27 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

On Wed, Oct 15, 2008 at 12:25 PM, D. Dante Lorenso <dantelarkspark.com> wrote:
> There's an awesome feature that was added to PostgreSQL a while back called
> RETURNING that allows you to make an INSERT, UPDATE, and DELETE statement
> behave like a SELECT statement. You can do something like this:
>
> INSERT INTO mytable (id, value)
> VALUES (1, 'something')
> RETURNING any_column_you_want;
>
> This would be equivalent to running something like this in MySQL:
>
> INSERT INTO mytable (id, value)
> VALUES (1, 'something');
>
> SELECT any_column_you_want
> FROM mytable
> WHERE id = 1;
>
> Here is another example with an UPDATE query:
>
> UPDATE mytable SET
> value = 'something'
> WHERE id = 1
> RETURNING id, other_number;
>
> The nice thing about this is that every insert or update can return any
> column you want (even multiple columns) without having to do the
> INSERT/UPDATE then turn around and perform another SELECT query.
>
> I want to use this because when I insert a value into a table, I don't
> always want to get the primary key returned to me. Sometimes I want another
> column which may contain a candidate key and I'd like to avoid the
> round-trip and additional logic incurred with running multiple queries.
>
> Does RETURNING exist in any current release of MySQL or is it on the TODO
> list even? If it's not, how can I go about asking to have it put on there?
>
> -- Dante
>
> ----------
> D. Dante Lorenso
> dantelarkspark.com

You can do your insert through a stored procedure and then at the end
do a select of those values.

http://dev.mysql.com/doc/refman/5.0/en/faqs-stored-procs.html#qandaitem-22-4-14
"22.4.14: Can MySQL 5.0 stored routines return result sets?

Stored procedures can, but stored functions cannot. If you perform an
ordinary SELECT inside a stored procedure, the result set is returned
directly to the client. You need to use the MySQL 4.1 (or above)
client-server protocol for this to work. This means that — for
instance — in PHP, you need to use the mysqli extension rather than
the old mysql extension. "

--
Rob Wultsch
wultschgmail.com
wultsch (aim)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]