You are viewing an older version of this section. View current production version.
TO_QUERY
Allows you to convert a dynamic SQL statement to a query type value.
The TO_QUERY function does not parameterize dynamic SQL queries, which results in unique plans being added to the in-memory and on-disk plancache. This can have a significant negative impact on query performance. If you use the TO_QUERY function extensively, care should be taken to manage the size of your plancache. See Managing Plancache Memory Usage for details on how to drop a plan from the in-memory plancache and delete the on-disk plancache files.
If you need to define a query-type variable from a static query that use stored procedure parameters (e.g. SELECT t.a, t.b FROM t WHERE t.c = x with x being the input parameter value), you can do that directly instead of using TO_QUERY. This will parameterize any literal values and re-use the same plan from the plancache the next time. For an example on how to do this, see Binding Variables to a Query Type Value.
Syntax
TO_QUERY ( select_statement )
Arguments
select_statement
A string containing a SELECT statement.
Return Type
String
Remarks
TO_QUERY may be used in an assignment statement where a query type variable is on the left side. It can also be used in a return statement of a function which returns a query type value, or as an argument to a function that accepts a query type value.
TO_QUERY and COLLECT are often used together to specify and run dynamic queries.
Examples
Using TO_QUERY in an assignment statement
In the example below, following the assignment to the query type variable, the procedure p returns the value of the query type variable. This value is returned only so that its contents can be written to the console using the ECHO statement.
DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
use memsql_docs_example;
CREATE TABLE t1(color TEXT);
CREATE TABLE t2(color TEXT);
INSERT INTO t1 VALUES ('red'), ('green'), ('blue');
INSERT INTO t2 VALUES ('orange'), ('blue'), ('purple');
DELIMITER //
CREATE PROCEDURE p(tbl TEXT)
RETURNS QUERY(color TEXT) AS
DECLARE
q QUERY(color TEXT) = TO_QUERY(CONCAT('SELECT color FROM ', tbl));
BEGIN
RETURN q;
END
//
DELIMITER ;
ECHO p('t2');
****
+--------+
| color |
+--------+
| purple |
| orange |
| blue |
+--------+
Using TO_QUERY in a RETURN statement
The example below, the procedure p returns a query type value.
DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
use memsql_docs_example;
CREATE TABLE t1(color TEXT);
CREATE TABLE t2(color TEXT);
INSERT INTO t1 VALUES ('red'), ('green'), ('blue');
INSERT INTO t2 VALUES ('orange'), ('blue'), ('purple');
DELIMITER //
CREATE PROCEDURE p(tbl TEXT)
RETURNS QUERY(color TEXT) AS
BEGIN
RETURN TO_QUERY(CONCAT('SELECT color FROM ', tbl));
END
//
DELIMITER ;
ECHO p('t1');
****
+-------+
| color |
+-------+
| green |
| red |
| blue |
+-------+
As a best practice, it is advisable to use TO_QUERY when the shape of query to be built (e.g. its table names, field names, and WHERE clause structure) will not be fully known until runtime.
In scenarios where the structure of the query is known, use statements like
q = SELECT a, b FROM t WHERE t.x = variable;
instead of
q = TO_QUERY(CONCAT("SELECT a, b FROM t WHERE t.x = ", variable));
The former is easier to read, and it should execute faster because it spends less time parsing at runtime.
Related Topics
- COLLECT: Executes a query and returns the result as an array of records.
- QUERY: A data type representing a
SELECTstatement. - SELECT: Retrieves data from a table.
