Sign inTry Free

TO_QUERY

On this page

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 singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
use singlestore_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 singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
use singlestore_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  |
+-------+

Note

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.

Database Binding Behavior for TO_QUERY() QTVs Within a Stored Procedure

For TO_QUERY query type values called inside a stored procedure, if the database is not specified, database binding is done at runtime.

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.sp1() AS
DECLARE
  q QUERY(val INT) = TO_QUERY("SELECT * FROM tmp");
  p QUERY(val INT) = TO_QUERY("SELECT * FROM db_test1.tmp");
BEGIN
  ECHO SELECT * FROM q;
  ECHO SELECT * FROM p;
END //


DELIMITER ;

Observe the query behavior by switching between the context databases with the USE command.

USE db_test1;
CALL db_test3.sp1();
+------+
| val  |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.12 sec)

+------+
| val  |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.12 sec)
USE db_test2;
CALL db_test3.sp1();
+------+
| val  |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.12 sec)

+------+
| val  |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.12 sec)

The queries (where database is not specified inside the stored procedure) use the currently selected database, which means that database binding happened 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.

Last modified: June 15, 2023

Was this article helpful?

On this page

Was this article helpful?