Join the SingleStore Community Today
Get expert advice, develop skills, and connect with others.

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

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 SELECT statement.
  • SELECT: Retrieves data from a table.