Syntax

variable_name = COLLECT(query_type_value)

variable_name = COLLECT(select_statement, QUERY(field_definition [, ...]))

  field_definition:
    field_name data_type [data_type_modifier]

Arguments

query_type_value

A query type value.

field_definition

The number of fields in the field_definition must be the same as the number of columns that the select_statement will return. The data_type(s) in the field_definition 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 field_name(s) in the field_definition also need not be the same as the field names in the select_statement.

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 resulting array.

If NOT NULL, indicates that field_name may not contain NULL values in the resulting array.

select_statement

A string containing a SQL SELECT statement.

Remarks

Some database systems implement cursors, which allow programmers to iterate through each row of a recordset and perform an action on the row. SingleStore does not use the term cursor. However, you can achieve read-only cursor functionality by calling COLLECT and iterating over the values in the resulting array. The array may be processed forwards, backwards or in an arbitrary order.

Expressions of the form record.field for a record type value cannot be substituted directly into a SQL statement in a stored procedure. To include a record field’s value in an SQL statement, assign the value into a variable, and use the variable in the SQL statement instead. This approach is used in example 1 below, with the variables _id and _name.

Example 1: Using COLLECT with Static Queries

In the following example, COLLECT uses a query type variable, whose definition SELECT * from t is static.

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;
CREATE TABLE t(id INT, name TEXT);
CREATE TABLE output_log(msg TEXT);

INSERT INTO t VALUES (1, 'red'), (2, 'green'), (3, 'blue');

DELIMITER //

CREATE OR REPLACE PROCEDURE p() AS
DECLARE  
  qry QUERY(id INT, name TEXT) = SELECT id, name FROM t;
  arr ARRAY(RECORD(id INT, name TEXT));
  _id INT;
  _name TEXT;
BEGIN
  arr = COLLECT(qry);
  FOR x in arr LOOP
    _id = x.id;
    _name = x.name;
    INSERT INTO output_log VALUES(CONCAT('[', _id, ', ', _name, ']'));
  END LOOP;
END //

DELIMITER ;

CALL p();

SELECT * FROM output_log ORDER BY msg;

+------------+
| msg        |
+------------+
| [1, red]   |
| [2, green] |
| [3, blue]  |
+------------+

DELIMITER //

CREATE OR REPLACE PROCEDURE p() AS
DECLARE
  qry QUERY(id INT, name TEXT) = SELECT id, name FROM t;
BEGIN
  FOR x IN COLLECT(qry) LOOP
    INSERT INTO output_log VALUES(CONCAT('[', x.id, ', ', x.name, ']'));
  END LOOP;
END //

DELIMITER ;

Example 2: Using COLLECT with Dynamic Queries (Method 1)

Here, you call COLLECT using a query type variable, whose value is populated by the TO_QUERY function. TO_QUERY allows you to write dynamic queries.

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;
CREATE TABLE t1(id INT, name TEXT);
CREATE TABLE t2(id INT, name TEXT);
CREATE TABLE output_log(msg TEXT);

INSERT INTO t1 VALUES (1, 'red'), (2, 'green'), (3, 'blue');
INSERT INTO t2 VALUES (1, 'orange'), (2, 'blue'), (3, 'purple');

DELIMITER //

CREATE OR REPLACE PROCEDURE p (tbl TEXT) AS
DECLARE  
  qry QUERY(id INT, name TEXT) = TO_QUERY(CONCAT('SELECT id, name FROM ' , tbl));
BEGIN
  FOR x IN COLLECT(qry) LOOP
    INSERT INTO output_log VALUES(CONCAT('[', x.id, ', ', x.name, ']'));
  END LOOP;
END //

DELIMITER ;

CALL p('t2');

SELECT * FROM output_log ORDER BY msg;

+-------------+
| msg         |
+-------------+
| [1, orange] |
| [2, blue]   |
| [3, purple] |
+-------------+

Example 3: Using COLLECT with Dynamic Queries (Method 2)

Here, you specify a dynamic query directly in a COLLECT statement. The first argument is the query, in text form. The second argument is a QUERY type which specifies the output schema of the query, so it is known at the time the COLLECT expression is compiled.

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
use singlestore_docs_example;
CREATE TABLE t1(id INT, name TEXT);
CREATE TABLE t2(id INT, name TEXT);
CREATE TABLE output_log(msg TEXT);

INSERT INTO t1 VALUES (1, 'red'), (2, 'green'), (3, 'blue');
INSERT INTO t2 VALUES (1, 'orange'), (2, 'blue'), (3, 'purple');

DELIMITER //
CREATE or REPLACE PROCEDURE p(tbl TEXT) AS
DECLARE  
BEGIN
  FOR x in COLLECT(CONCAT('SELECT id, name FROM ' , tbl), QUERY(id INT, name TEXT)) LOOP
    INSERT INTO output_log VALUES(CONCAT('[', x.id, ', ', x.name, ']'));
  END LOOP;
END //
DELIMITER ;

CALL p('t2');

SELECT * FROM output_log ORDER BY msg;

+-------------+
| msg         |
+-------------+
| [1, orange] |
| [2, blue]   |
| [3, purple] |
+-------------+

Related Topics

• CREATE PROCEDURE: The CREATE PROCEDURE command creates a stored procedure.

• QUERY: A data type representing a SELECT statement.

• TO_QUERY: Converts a SQL string to a query type value.