QUERY
SingleStoreDB 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_definition
s.
The number of field_definition
s must be the same as the number of columns that the select_statement
will return.
The data_type
s in the field_definition
s 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_name
s 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