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,
https://www.valentina-db.com/en/discussions/4901-arrays-in-stored-procedures

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.

Relationships

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

Activities

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