MemSQL Procedural SQL (MPSQL) supports the QUERY data type, whose value represents a SQL SELECT statement, optionally with bound variable values. Query type values may be returned from a stored procedure, assigned as a value into a variable, passed as a parameter to a stored procedure, evaluated to return a rowset to a client using ECHO, or evaluated using COLLECT or SCALAR functions.

TO_QUERY may be used to convert a dynamic SELECT statement to a query type.

Query Type Definition Syntax

The following syntax declares a query type value.

name QUERY(field_definition [, ...]) [ = select_statement]

  field_definition:
    field_name data_type [data_type_modifier]

Arguments

name

The name of the query variable or parameter.

field_definition

A query type declaration will contain one or more field_definitions.

The number of field_definitions must be the same as the number of columns that the select_statement will return.

The data_types in the field_definitions do not have to be the same as the data types of the columns that the select_statement will return. If the data types do not match, the :> operator will automatically be used to convert the column type to the field type.

Field names specified in the select_statement are irrelevant; The field_names in the field_definition are instead used in the QUERY’s result set.

field_name

The name of the field.

data_type

Any scalar-valued data type. For a complete list of data types, see the Data Types topic.

data_type_modifier

If not present or NULL, indicates that field_name may contain NULL values in the QUERY’s result set.

If NOT NULL, indicates that field_name may not contain NULL values in the QUERY’s result set.

select_statement

A SQL SELECT statement, optionally referencing parameters and variables from the current stored procedure. If the SELECT statement is built dynamically using a string, the string should be passed to TO_QUERY. TO_QUERY will convert the string to a query type value.

The select_statement is optional because it does not need to be specified when the QUERY is declared. The select_statement may be assigned to the query variable at a later time.

Remarks

After a query variable has been declared, new fields cannot be added and existing fields cannot be changed.

When calling a procedure, input parameters and return types are always passed by value. This behavior applies to all types, including queries.

Declaration and Initialization

Query types are declared in a procedure as either an input parameter or as a variable in the DECLARE block. For example, the function below uses both a query type variable and a query type parameter, and returns one or the other, at random:

DELIMITER //
CREATE OR REPLACE PROCEDURE p(x QUERY(a INT, b VARCHAR(30))) -- x is a query type parameter
RETURNS QUERY(a INT, b VARCHAR(30)) AS
DECLARE
  -- q is a query type variable
  q QUERY(a INT, b VARCHAR(30)) = SELECT 1, "red";
BEGIN
  IF rand() > 0.5 THEN
    RETURN q;
  END IF;
  RETURN x;
END //
DELIMITER ;

Assignment

You may assign a value to a query type variable in the declaration of the variable using =, or :=. The value assigned may be a SELECT statement, a query variable, or a query parameter. You may assign a value to a query variable in the body of a procedure using = or :=.

Query Type Example

The following example demonstrates how to pass a query as an input parameter to a procedure, assign a query result from a stored procedure into a query type variable, and conditionally assign different values into a query type variable.

DELIMITER //
CREATE OR REPLACE PROCEDURE p(
  x QUERY(a INT, b VARCHAR(30)),
  a INT)
RETURNS QUERY(a INT, b VARCHAR(30)) AS
DECLARE
  q QUERY(a INT, b VARCHAR(30)) = SELECT 1, "purple";
BEGIN
  IF a = 0 THEN
    RETURN q;
  ELSE
    RETURN x;
  END IF;
END //

CREATE OR REPLACE PROCEDURE q(i INT)
RETURNS QUERY(a INT, b VARCHAR(30)) AS
DECLARE
  v QUERY(a INT, b VARCHAR(30)) = SELECT 2, "yellow";
  l QUERY(a INT, b VARCHAR(30)) = SELECT null, null;
BEGIN
  l = p(v, i);
  RETURN l;
END //
DELIMITER ;

ECHO q(0);

+------+--------+
| a    | b      |
+------+--------+
|    1 | purple |
+------+--------+

ECHO q(1);

+------+--------+
| a    | b      |
+------+--------+
|    2 | yellow |
+------+--------+

Binding Variables to a Query Type Value

Query variable and parameter values may include both a query statement as well as bindings of variables. Both scalar variable types and QUERY variable types can be bound. For example:

CREATE TABLE t(a INT, b VARCHAR(30));
INSERT t VALUES(1, "red"), (2, "blue"), (3, "green");

DELIMITER //
CREATE OR REPLACE PROCEDURE p2(n INT)
  RETURNS QUERY(a INT, b VARCHAR(30)) AS
DECLARE q QUERY(a INT, b VARCHAR(30)) = SELECT a, b FROM t WHERE t.a >= n;
BEGIN
  RETURN q;
END //
DELIMITER ;

ECHO p2(1);

+------+-------+
| a    | b     |
+------+-------+
|    3 | green |
|    2 | blue  |
|    1 | red   |
+------+-------+

ECHO p2(2);

+------+-------+
| a    | b     |
+------+-------+
|    3 | green |
|    2 | blue  |
+------+-------+

In the first call to p2, the query returned is

SELECT a, b FROM t WHERE t.a >= 1

which produces 3 rows of output. In the second call to p2, the query returned is

SELECT a, b FROM t WHERE t.a >= 2

which produces 2 rows of output. The variable a at the time of the call is bound into the query prior to returning the query.

Late Binding

MPSQL performs late binding of queries assigned to query type variables or returned by stored procedures. This means that the results of the query are not produced until they are needed due to an ECHO call, or by use of the functions SCALAR or COLLECT.

Capture Semantics

QUERY type variables use the original value of each stored procedure variable they reference. This means that the value used within the body of the QUERY will be the value that the variable had at QUERY type variable creation time, not QUERY type variable execution time.

Using Query Type Values In Queries

You can use query type values in other queries similar to the way you would reference a view. A query type variable or parameter name can be used in another query in a stored procedure. The query type value will be expanded inline into the enclosing query, the same way a view is expanded.

For example, the stored procedure defined below declares query type variable q and uses it in an INSERT ... SELECT ... statement to add data to the table called target.

CREATE TABLE source(a int, b int);
CREATE TABLE target(a int, b int);
INSERT INTO source VALUES(1, 2), (2, 2), (3, 2), (4, 3), (5, 3);

DELIMITER //
CREATE OR REPLACE PROCEDURE p() AS
DECLARE
    q QUERY(a int, b int) = SELECT a, b FROM source WHERE b = 2;
BEGIN
    INSERT INTO target SELECT a, b from q;
END
//
DELIMITER ;

memsql> CALL p();
Query OK, 0 rows affected (0.35 sec)

memsql> SELECT * FROM target;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
+------+------+

You can also assign values to query type variables in the body of a stored procedure, instead of the DECLARE block. For example,

CREATE TABLE t(a int, b int);

INSERT t VALUES(1,2),(3,4),(5,6);

DELIMITER //

CREATE OR REPLACE PROCEDURE ptest(x INT) AS
DECLARE
  q QUERY(a INT, b INT);
BEGIN
  IF x < 0 THEN
    q = SELECT a, b FROM t;
  ELSE
    q = SELECT a, b FROM t WHERE a = x;
  END IF;
  echo SELECT a, b FROM q;
END //

DELIMITER ;

CALL ptest(1);
****
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+

CALL ptest(-1);
****
+------+------+
| a    | b    |
+------+------+
|    3 |    4 |
|    5 |    6 |
|    1 |    2 |
+------+------+

Related Topics

• COLLECT: Executes a query and returns the result as an array of records.
• ECHO: Executes a stored procedure and outputs a set of rows as a result.
• SCALAR: Executes a query that returns a single value.
• TO_QUERY: Converts a SQL string to a query type value.

Was this Article Helpful?

Thanks For Your Feedback!