QUERY

Query types are declared in a procedure as either an input parameter or as a variable in the DECLARE block. For example, the function below uses both a query type variable and a query type parameter, and returns one or the other, at random:

DELIMITER //
CREATE OR REPLACE PROCEDURE p(x QUERY(a INT, b VARCHAR(30))) -- x is a query type parameter
RETURNS QUERY(a INT, b VARCHAR(30)) AS
DECLARE
  -- q is a query type variable
  q QUERY(a INT, b VARCHAR(30)) = SELECT 1, "red";
BEGIN
  IF rand() > 0.5 THEN
    RETURN q;
  END IF;
  RETURN x;
END //
DELIMITER ;

You may assign a value to a query type variable in the declaration of the variable using =, or :=. The value assigned may be a SELECT statement, a query variable, or a query parameter. You may assign a value to a query variable in the body of a procedure using = or :=.

DELIMITER //
CREATE OR REPLACE PROCEDURE p(
  x QUERY(a INT, b VARCHAR(30)),
  a INT)
RETURNS QUERY(a INT, b VARCHAR(30)) AS
DECLARE
  q QUERY(a INT, b VARCHAR(30)) = SELECT 1, "purple";
BEGIN
  IF a = 0 THEN
    RETURN q;
  ELSE
    RETURN x;
  END IF;
END //

CREATE OR REPLACE PROCEDURE q(i INT)
RETURNS QUERY(a INT, b VARCHAR(30)) AS
DECLARE
  v QUERY(a INT, b VARCHAR(30)) = SELECT 2, "yellow";
  l QUERY(a INT, b VARCHAR(30)) = SELECT null, null;
BEGIN
  l = p(v, i);
  RETURN l;
END //
DELIMITER ;

ECHO q(0);

+------+--------+
| a    | b      |
+------+--------+
|    1 | purple |
+------+--------+

ECHO q(1);

+------+--------+
| a    | b      |
+------+--------+
|    2 | yellow |
+------+--------+

Query variable and parameter values may include both a query statement as well as bindings of variables. Both scalar variable types and QUERY variable types can be bound. For example:

CREATE TABLE t(a INT, b VARCHAR(30));
INSERT t VALUES(1, "red"), (2, "blue"), (3, "green");

DELIMITER //
CREATE OR REPLACE PROCEDURE p2(n INT)
  RETURNS QUERY(a INT, b VARCHAR(30)) AS
DECLARE q QUERY(a INT, b VARCHAR(30)) = SELECT a, b FROM t WHERE t.a >= n;
BEGIN
  RETURN q;
END //
DELIMITER ;

ECHO p2(1);

+------+-------+
| a    | b     |
+------+-------+
|    3 | green |
|    2 | blue  |
|    1 | red   |
+------+-------+

ECHO p2(2);

+------+-------+
| a    | b     |
+------+-------+
|    3 | green |
|    2 | blue  |
+------+-------+

In the first call to p2, the query returned is

SELECT a, b FROM t WHERE t.a >= 1

which produces 3 rows of output. In the second call to p2, the query returned is

SELECT a, b FROM t WHERE t.a >= 2

which produces 2 rows of output. The variable a at the time of the call is bound into the query prior to returning the query.

PSQL performs late binding of queries assigned to query type variables or returned by stored procedures. This means that the results of the query are not produced until they are needed due to an ECHO call, or by use of the functions SCALAR or COLLECT.

QUERY type variables use the original value of each stored procedure variable they reference. This means that the value used within the body of the QUERY will be the value that the variable had at QUERY type variable creation time, not QUERY type variable execution time.

You can use query type values in other queries similar to the way you would reference a view. A query type variable or parameter name can be used in another query in a stored procedure. The query type value will be expanded inline into the enclosing query, the same way a view is expanded.

For example, the stored procedure defined below declares query type variable q and uses it in an INSERT ... SELECT ... statement to add data to the table called target.

CREATE TABLE source(a int, b int);
CREATE TABLE target(a int, b int);
INSERT INTO source VALUES(1, 2), (2, 2), (3, 2), (4, 3), (5, 3);

DELIMITER //

CREATE OR REPLACE PROCEDURE p() AS
DECLARE
    q QUERY(a int, b int) = SELECT a, b FROM source WHERE b = 2;
BEGIN
    INSERT INTO target SELECT a, b from q;
END //

DELIMITER ;

CALL p();
SELECT * FROM target;
****
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
+------+------+

In the example above, notice that the query type variable q is used in the FROM clause. You can also apply filters, joins, etc. to QTVs in a query, similar to a view. For example,

DELIMITER //
CREATE OR REPLACE PROCEDURE p() AS
DECLARE
    q QUERY(a int, b int) = SELECT a, b FROM source;
BEGIN
    ECHO SELECT * FROM q WHERE a < 3 ORDER BY a ASC;
END //
DELIMITER ;

call p();
****
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    2 |    2 |
+------+------+

You can also assign values to query type variables in the body of a stored procedure, instead of the DECLARE block. For example,

CREATE TABLE t(a int, b int);

INSERT t VALUES(1,2),(3,4),(5,6);

DELIMITER //

CREATE OR REPLACE PROCEDURE ptest(x INT) AS
DECLARE
  q QUERY(a INT, b INT);
BEGIN
  IF x < 0 THEN
    q = SELECT a, b FROM t;
  ELSE
    q = SELECT a, b FROM t WHERE a = x;
  END IF;
  echo SELECT a, b FROM q;
END //

DELIMITER ;

CALL ptest(1);
****
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+

CALL ptest(-1);
****
+------+------+
| a    | b    |
+------+------+
|    3 |    4 |
|    5 |    6 |
|    1 |    2 |
+------+------+

If a database is not specified in a QTV within a stored procedure, the query uses the database that the stored procedure was defined in.

The following example explains this behavior.

CREATE DATABASE db_test1;
CREATE DATABASE db_test2;
CREATE DATABASE db_test3;

CREATE TABLE db_test1.tmp(val INT);
INSERT INTO db_test1.tmp VALUES (1),(2);

CREATE TABLE db_test2.tmp(val INT);
INSERT INTO db_test2.tmp VALUES (3),(4);

CREATE TABLE db_test3.tmp(val INT);
INSERT INTO db_test3.tmp VALUES (5),(6);

DELIMITER //

CREATE OR REPLACE PROCEDURE db_test3.sp() AS
DECLARE
  q QUERY(val INT) = SELECT * FROM tmp;
  p QUERY(val INT) = SELECT SUM(val) FROM db_test1.tmp;
BEGIN
  ECHO SELECT * FROM q;
  ECHO SELECT * FROM p;
END //

DELIMITER ;

Change the context database to db_test1 and call the stored procedure.

USE db_test1;
CALL db_test3.sp();
****
+------+
| val  |
+------+
|    5 |
|    6 |
+------+
2 rows in set (0.01 sec)

+------+
| val  |
+------+
|    3 |
+------+
1 row in set (0.01 sec)

Now, change the context database to db_test2 and call the stored procedure.

USE db_test2;
CALL db_test3.sp();
****
+------+
| val  |
+------+
|    5 |
|    6 |
+------+
2 rows in set (0.01 sec)

+------+
| val  |
+------+
|    3 |
+------+
1 row in set (0.28 sec)

Instead of using the currently selected database (specified with USE), the query SELECT * FROM tmp; uses the definer’s database (the database specified while defining the stored procedure).

Related Topics