Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
MDEV-5092 adds support for:
UPDATE ... RETURNING ... |
However, the returned values are returned as a result set rather than being assignable to local stored procedure variables.
The following works:
CREATE TABLE t1 (id1 INT, val1 VARCHAR(3)); |
INSERT INTO t1 VALUES (1, 'abc'); |
|
|
CREATE PROCEDURE test_update_returning() |
BEGIN
|
UPDATE t1 |
SET val1 = 'xyz' |
WHERE id1 = 1 |
RETURNING *;
|
END| |
|
|
CALL test_update_returning();
|
This returns:
id1 val1
|
1 xyz
|
However, the following does not compile:
CREATE PROCEDURE test_update_returning_into() |
BEGIN
|
DECLARE v_id INT; |
DECLARE v_val VARCHAR(3); |
|
|
UPDATE t1 |
SET val1 = 'xyz' |
WHERE id1 = 1 |
RETURNING id1, val1 INTO v_id, v_val; |
|
|
SELECT v_id, v_val; |
END| |
It fails with:
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO v_id, v_val;
|
SELECT v_id, v_val;
|
END'
|
Requested enhancement enhancement is to add support for assigning values from UPDATE RETURNING directly into local stored procedure variables with UPDATE ... RETURNING ... INTO
UPDATE t1 |
SET val1 = 'xyz' |
WHERE id1 = 1 |
RETURNING id1, val1 INTO v_id, v_val; |
This would make UPDATE RETURNING usable in stored procedures where the returned values are used rather than returned to the client.
For example, it would allow:
SELECT d_next_o_id, d_tax |
INTO no_d_next_o_id, no_d_tax |
FROM district |
WHERE d_id = no_d_id |
AND d_w_id = no_w_id |
FOR UPDATE; |
|
|
UPDATE district |
SET d_next_o_id = d_next_o_id + 1 |
WHERE d_id = no_d_id |
AND d_w_id = no_w_id; |
to be replaced with a single statement such as:
UPDATE district |
SET d_next_o_id = d_next_o_id + 1 |
WHERE d_id = no_d_id |
AND d_w_id = no_w_id |
RETURNING OLD_VALUE(d_next_o_id), d_tax
|
INTO no_d_next_o_id, no_d_tax; |