Rob Wultsch wrote:
On Wed, Oct 15, 2008 at 12:25 PM, D. Dante Lorenso <[EMAIL PROTECTED]> 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
[EMAIL PROTECTED]
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. "
This is an interesting strategy in that all your queries would turn into
CALL statements.
There are several reasons why I would NOT want to turn all my queries
into stored procedures, though. The main problem I have is that it is
difficult to deploy stored procedures from DEV to PROD environments and
have those deployments synchronized with the deployment of the web code.
SQL which is kept with the application is easily deployed when the
application is deployed and the same goes for version control of the SQL
if you are using something like Subversion to maintain change history.
So, I suppose you CAN perform an UPDATE and run a SELECT from a stored
procedure, but this strategy is not much better than doing both calls
from the client and still does not act like the RETURNING feature I was
hoping for.
-- Dante
----------
D. Dante Lorenso
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
