On Wed, Oct 15, 2008 at 2:00 PM, D. Dante Lorenso <[EMAIL PROTECTED]> wrote: > 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
For the record I am not a big fan of stored procedures, particularly because the svn/cvn issues. Also debugging is a bear. However, what I am suggesting is not to run an update and then a SELECT ... FROM ...(unless you are using triggers, or a virtual columns when that is merged, etc) because you will already have all the values passed in as variables you should be able to do something like: SELECT var1 AS 'colname', var2 AS 'col2'; (Note the lack of a FROM clause.) Alternatively, you could set a user defined variable in insert triggers and then reuse the variables later on. Other than the above strategies I think you are probably out of luck. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
