TO_QUERY

Allows you to convert a dynamic SQL statement to a query type value.

Warning

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. If you use this function extensively, care should be taken to manage the size of your plancache. See Managing Plancache Memory Usage for more details on how to drop a plan from the in-memory plancache and how to delete the plancache files on disk.

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.

SET sql_mode = 'PIPES_AS_CONCAT';

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('SELECT color FROM ' || tbl);
BEGIN
  RETURN q;
END
//
DELIMITER ;

ECHO p('t2');

Output:

+--------+
| color  |
+--------+
| purple |
| orange |
| blue   |
+--------+

Using TO_QUERY in a RETURN statement

The example below, the procedure p returns a query type value.

SET sql_mode = 'PIPES_AS_CONCAT';

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('SELECT color FROM ' || tbl);
END
//
DELIMITER ;

ECHO p('t1');

Output:

+-------+
| color |
+-------+
| green |
| red   |
| blue  |
+-------+

Related Topics

  • COLLECT: Executes a query and returns the result as an array of records.
  • QUERY: A data type representing a SELECT statement.
  • SELECT: Retrieves data from a table.
Was this article useful?