View Issue Details

IDProjectCategoryView StatusLast Update
0008325VCOMPONENT-VKERNELSQL Stored Procedurespublic2019-01-23 11:31
ReporterIvan SmahinAssigned ToRuslan Zasukhin 
PrioritynormalSeverityminorReproducibilityhave not tried
Status resolvedResolutionfixed 
Product Version 
Target Version9.0.xFixed in Version9.0.x 
Summary0008325: [SQL] Arrays in stored procedures.
DescriptionReported by Scott Roberts,

Is it possible to use an array variable in a stored procedure? The SQL Editor lets me create the following DECLARE statement but I am unable to determine how to set or get the elements of the array.

DECLARE v_days_of_week BOOLEAN[7];

-- This line returns NULL
PRINT v_days_of_week[2];

-- This line causes Kernel error: 0x71000. unexpected token v_days_of_week
SET v_days_of_week[2] = TRUE;

-- This line causes Kernel error: 0xa000. "Getting expression from value of ARRAY type" is not supported.
SET v_days_of_week = ARRAY[false,true,false,false,false,false,false];

I'm wondering if it is possible to retrieve an array field from a table and store it in a variable. All of the examples for Array functions in the documentation use literals for the array values (and none of them stores the array in a variable). I'm hoping that I can retrieve an array from a table and then loop through the array in a stored procedure. I'm also hoping that I can put the value of another variable into an element of an array.

My workaround thus far is to concatenate the elements of the array into a comma-delimited string and use the List... functions to access the elements. I'm wondering if there is another (better) way to do this.
TagsNo tags attached.


related to 0008348 resolvedIvan Smahin [SQL] Variants in stored procedures. 


Ruslan Zasukhin

Ruslan Zasukhin

2019-01-21 15:24

administrator   ~0010644

rev. afd72f2c3b93c1df2192963fa55b124cb4c16dcb

8325: [VSQL] arrays in stored procedures. Parser and TreeParser now accept SET fld[2] = .. and pass this to factory and it to node ctor.

Ivan have write in 8325 that low level is done, so can be tested now.

branches release/9.0, origin/release/9.0

Issue History

Date Modified Username Field Change
2018-07-27 08:09 Ivan Smahin New Issue
2018-07-27 08:09 Ivan Smahin Status new => assigned
2018-07-27 08:09 Ivan Smahin Assigned To => Ivan Smahin
2018-08-07 06:25 Ivan Smahin Assigned To Ivan Smahin => Ruslan Zasukhin
2018-08-22 08:37 Ivan Smahin Relationship added related to 0008348
2019-01-21 15:24 Ruslan Zasukhin Note Added: 0010644
2019-01-23 08:11 Ivan Smahin Status assigned => resolved
2019-01-23 08:11 Ivan Smahin Fixed in Version => 9.0.x
2019-01-23 08:11 Ivan Smahin Resolution open => fixed
2019-01-23 11:31 Ruslan Zasukhin Target Version 8.7.x => 9.0.x