TO_QUERY

Allows you to convert a dynamic SQL statement 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?