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.
Note: Queries that are built from the TO_QUERY() function and query type values are parameterized when they are run, which allows plans to be reused in the plancache.
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.
