QUERY

SingleStore Procedural SQL (PSQL) supports the QUERY data type, whose value represents a SQL SELECT statement, optionally with bound variable values.

Query type values may be returned from a stored procedure, assigned as a value into a variable, passed as a parameter to a stored procedure, evaluated to return a rowset to a client using ECHO, or evaluated using COLLECT or SCALAR functions.

TO_QUERY may be used to convert a dynamic SELECT statement to a query type.

Query Type Definition Syntax

The following syntax declares a query type value.

name QUERY(field_definition [, ...]) [ = select_statement]

  field_definition:
    field_name data_type [data_type_modifier]

Arguments

name

The name of the query variable or parameter.

field_definition

A query type declaration will contain one or more field_definitions.

The number of field_definitions must be the same as the number of columns that the select_statement will return.

The data_types in the field_definitions do not have to be the same as the data types of the columns that the select_statement will return. If the data types do not match, the :> operator will automatically be used to convert the column type to the field type.

Field names specified in the select_statement are irrelevant; The field_names in the field_definition are instead used in the QUERY’s result set.

field_name

The name of the field.

data_type

Any scalar-valued data type. For a complete list of data types, see the Data Types topic.

data_type_modifier

If not present or NULL, indicates that field_name may contain NULL values in the QUERY’s result set.

If NOT NULL, indicates that field_name may not contain NULL values in the QUERY’s result set.

select_statement

A SQL SELECT statement, optionally referencing parameters and variables from the current stored procedure. If the SELECT statement is built dynamically using a string, the string should be passed to TO_QUERY. TO_QUERY will convert the string to a query type value.

The select_statement is optional because it does not need to be specified when the QUERY is declared. The select_statement may be assigned to the query variable at a later time.

Remarks

After a query variable has been declared, new fields cannot be added and existing fields cannot be changed.

When calling a procedure, input parameters and return types are always passed by value. This behavior applies to all types, including queries.

Declaration and Initialization

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 ;

Assignment

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 :=.

Query Type Example

The following example demonstrates how to pass a query as an input parameter to a procedure, assign a query result from a stored procedure into a query type variable, and conditionally assign different values into a query type variable.

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 |
+------+--------+

Binding Variables to a Query Type Value

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.

Late Binding

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.

Capture Semantics

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.

Using Query Type Values In Queries

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 |
+------+------+

Database Binding Behavior for QTVs Within a Stored Procedure

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

  • COLLECT: Executes a query and returns the result as an array of records.

  • ECHO: Executes a stored procedure and outputs a set of rows as a result.

  • SCALAR: Executes a query that returns a single value.

  • TO_QUERY: Converts a SQL string to a query type value.

Last modified: June 12, 2023

Was this article helpful?