COLLECT

The COLLECT function executes a SELECT statement having the QUERY type and stores the result as an array of records.

After COLLECT executes you will typically iterate through the array to perform processing on its elements.

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]  |
+------------+

Note

Instead of using variable assignments and verbose definitions, you can shorten the procedure definition (and body) by inserting the COLLECT function directly inside the loop as:

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 ;

The next example demonstrates the use of this style of code.

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.

Warning

The TO_QUERY function does not parameterize dynamic SQL queries, which results in unique plans being added to the in-memory and on-disk plancache. If you use this function extensively, care should be taken to manage the size of your plancache.

See Managing Plancache Memory and Disk Usage for more details on how to drop a plan from the in-memory plancache and how to delete the plancache files on disk.

If you need to define a query-type variable from a static query that use stored procedure parameters (e.g. SELECT t.a, t.b FROM t WHERE t.c = x with x being the input parameter value), you can do that directly instead of using TO_QUERY. This will parameterize any literal values and re-use the same plan from the plancache the next time. For an example of how to do this, see Binding Variables to a Query Type Value.

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.

Last modified: April 4, 2023

Was this article helpful?