On Wed, Oct 15, 2008 at 2:09 PM, Rob Wultsch <[EMAIL PROTECTED]> wrote:
> 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.
Example:
mysql> use test;
Database changed
mysql> create table t1(c int);
Query OK, 0 rows affected (0.08 sec)
mysql> create table t2(c int);
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TRIGGER triggy AFTER INSERT
-> ON t1 FOR EACH ROW
-> set @c=NEW.c;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO t1 values(3);
Query OK, 1 row affected (0.06 sec)
mysql> insert into t2 values(@c);
Query OK, 1 row affected (0.05 sec)
mysql> select * from t2;
+------+
| c |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
--
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]
