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.

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

The QUERY type variable must be declared so that the number of fields is the same as the number of columns that the select_statement will return. The data types of the QUERY type variable fields 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. The QUERY variable field names also do not have to be the same as the select_statement column names.

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

The optional modifier for the data type, such as NULL or NOT NULL. These modifiers only apply to query result column type definitions. A query type itself is always NOT NULL, and may not be declared with a data type modifier.

select_statement

A SQL SELECT statement, optionally referencing parameters and variables from the current stored procedure.

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 ;

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

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

Related Topics

• ECHO: The ECHO command is often used to evaluate a query returned by a procedure and send the resulting rows to the client.
• SCALAR: Returns a single value produced by a query that yields one row with one column.
• COLLECT: Returns a collection (array) of records produced by a query.

Was this Article Helpful?

Thanks For Your Feedback!