Error when executing a stored procedure

ramon_perea

Hello, We are developing some stored procedures at Version 11. In the code we call a FOR QUERY statement,
FOR field1,field2,field3,field4 IN QUERY SELECT field1,field2,field3,field4 FROM sometable LOOP RAISE NOTICE 'for returned %, %, %, %, FOUND=%',field1,field2,field3,field4, counter; counter:=counter+1; END LOOP;
The stored was created without problem, but when it's executed throw the next error:
SQL Error [10322] [0A000]: [Vertica][VJDBC](10322) ERROR: Executing FOR (QUERY) is not yet supported

The documentation said this statement is supported, someone can we help us?

Jim_Knicely

@ramon_perea - The abillity to use a FOR QUERY in Vertica was introduced in 11.0.0-1.

Example:

dbadmin=> SELECT version();
               version
-------------------------------------
 Vertica Analytic Database v11.0.0-1
(1 row)

dbadmin=> SELECT * FROM foo;
 id | first_name | last_name |  hire_dt
----+------------+-----------+------------
  1 | Jim        | K         | 2021-11-24
  2 | Jane       | L         | 2021-11-24
  3 | Josh       | K         | 2021-11-24
(3 rows)

dbadmin=> DO $$
dbadmin$> DECLARE
dbadmin$> field1 VARCHAR;
dbadmin$> field2 VARCHAR;
dbadmin$> field3 VARCHAR;
dbadmin$> field4 VARCHAR;
dbadmin$> counter INTEGER:=0;
dbadmin$> BEGIN
dbadmin$> FOR field1,field2,field3,field4 IN QUERY
dbadmin$> SELECT
dbadmin$>   id::INTEGER
dbadmin$> , first_name
dbadmin$> , last_name
dbadmin$> , hire_dt
dbadmin$>   FROM foo
dbadmin$>   LIMIT 10
dbadmin$> LOOP
dbadmin$>   RAISE NOTICE 'for returned %, %, %, %, FOUND=%',field1,field2,field3,field4, counter; counter:=counter+1;
dbadmin$> END LOOP;
dbadmin$> END;
dbadmin$> $$
dbadmin-> ;
NOTICE 2005:  for returned 1, Jim, K, 2021-11-24, FOUND=0
NOTICE 2005:  for returned 2, Jane, L, 2021-11-24, FOUND=1
NOTICE 2005:  for returned 3, Josh, K, 2021-11-24, FOUND=2
DO

Jim_Knicely

What version of 11 are you running?

ramon_perea

@Jim_Knicely said:
What version of 11 are you running?

Hi Jim

Vertica Analytic Database v11.0.0-0

marcothesane

Works with this one: ....

DO $$
DECLARE
field1 VARCHAR;
field2 VARCHAR;
field3 VARCHAR;
field4 VARCHAR;
counter INTEGER:=0;
BEGIN
FOR field1,field2,field3,field4 IN QUERY
SELECT
  id::INTEGER
, first_name
, last_name
, hire_dt
  FROM foo
  LIMIT 10
LOOP
  RAISE NOTICE 'for returned %, %, %, %, FOUND=%',field1,field2,field3,field4, counter; counter:=counter+1;
END LOOP;
END;
$$
-- out NOTICE 2005:  for returned 1, Arthur, Dent, 2017-02-05 00:00:00, FOUND=0
-- out NOTICE 2005:  for returned 2, Ford, Prefect, 2017-02-05 00:00:00, FOUND=1
-- out NOTICE 2005:  for returned 3, Zaphod, Beeblebrox, 2017-02-05 00:00:00, FOUND=2
-- out NOTICE 2005:  for returned 4, Tricia, McMillan, 2017-02-05 00:00:00, FOUND=3
-- out NOTICE 2005:  for returned 5, Gag, Halfrunt, 2017-02-05 00:00:00, FOUND=4
-- out NOTICE 2005:  for returned 6, Prostetnic Vogon, Jeltz, 2017-02-05 00:00:00, FOUND=5
-- out NOTICE 2005:  for returned 7, Lionel, Prosser, 2017-02-05 00:00:00, FOUND=6
-- out NOTICE 2005:  for returned 8, Benji, Mouse, 2017-02-05 00:00:00, FOUND=7
-- out NOTICE 2005:  for returned 9, Frankie, Mouse, 2017-02-05 00:00:00, FOUND=8
-- out NOTICE 2005:  for returned 10, Svlad, Cjelli, 2017-02-05 00:00:00, FOUND=9
SELECT VERSION();                                                                                                                                                                    
-- out                VERSION               
-- out -------------------------------------
-- out  Vertica Analytic Database v11.0.1-0

ramon_perea

@marcothesane said:
Works with this one: ....

DO $$
DECLARE
field1 VARCHAR;
field2 VARCHAR;
field3 VARCHAR;
field4 VARCHAR;
counter INTEGER:=0;
BEGIN
FOR field1,field2,field3,field4 IN QUERY
SELECT
  id::INTEGER
, first_name
, last_name
, hire_dt
  FROM foo
  LIMIT 10
LOOP
  RAISE NOTICE 'for returned %, %, %, %, FOUND=%',field1,field2,field3,field4, counter; counter:=counter+1;
END LOOP;
END;
$$
-- out NOTICE 2005:  for returned 1, Arthur, Dent, 2017-02-05 00:00:00, FOUND=0
-- out NOTICE 2005:  for returned 2, Ford, Prefect, 2017-02-05 00:00:00, FOUND=1
-- out NOTICE 2005:  for returned 3, Zaphod, Beeblebrox, 2017-02-05 00:00:00, FOUND=2
-- out NOTICE 2005:  for returned 4, Tricia, McMillan, 2017-02-05 00:00:00, FOUND=3
-- out NOTICE 2005:  for returned 5, Gag, Halfrunt, 2017-02-05 00:00:00, FOUND=4
-- out NOTICE 2005:  for returned 6, Prostetnic Vogon, Jeltz, 2017-02-05 00:00:00, FOUND=5
-- out NOTICE 2005:  for returned 7, Lionel, Prosser, 2017-02-05 00:00:00, FOUND=6
-- out NOTICE 2005:  for returned 8, Benji, Mouse, 2017-02-05 00:00:00, FOUND=7
-- out NOTICE 2005:  for returned 9, Frankie, Mouse, 2017-02-05 00:00:00, FOUND=8
-- out NOTICE 2005:  for returned 10, Svlad, Cjelli, 2017-02-05 00:00:00, FOUND=9
SELECT VERSION();                                                                                                                                                                    
-- out                VERSION               
-- out -------------------------------------
-- out  Vertica Analytic Database v11.0.1-0

Hello.

The same error:

SQL Error [10322] [0A000]: [Vertica]VJDBC ERROR: Executing FOR (QUERY) is not yet supported

ramon_perea

@Jim_Knicely said:
@ramon_perea - The abillity to use a FOR QUERY in Vertica was introduced in 11.0.0-1.

Example:

dbadmin=> SELECT version();
               version
-------------------------------------
 Vertica Analytic Database v11.0.0-1
(1 row)

dbadmin=> SELECT * FROM foo;
 id | first_name | last_name |  hire_dt
----+------------+-----------+------------
  1 | Jim        | K         | 2021-11-24
  2 | Jane       | L         | 2021-11-24
  3 | Josh       | K         | 2021-11-24
(3 rows)

dbadmin=> DO $$
dbadmin$> DECLARE
dbadmin$> field1 VARCHAR;
dbadmin$> field2 VARCHAR;
dbadmin$> field3 VARCHAR;
dbadmin$> field4 VARCHAR;
dbadmin$> counter INTEGER:=0;
dbadmin$> BEGIN
dbadmin$> FOR field1,field2,field3,field4 IN QUERY
dbadmin$> SELECT
dbadmin$>   id::INTEGER
dbadmin$> , first_name
dbadmin$> , last_name
dbadmin$> , hire_dt
dbadmin$>   FROM foo
dbadmin$>   LIMIT 10
dbadmin$> LOOP
dbadmin$>   RAISE NOTICE 'for returned %, %, %, %, FOUND=%',field1,field2,field3,field4, counter; counter:=counter+1;
dbadmin$> END LOOP;
dbadmin$> END;
dbadmin$> $$
dbadmin-> ;
NOTICE 2005:  for returned 1, Jim, K, 2021-11-24, FOUND=0
NOTICE 2005:  for returned 2, Jane, L, 2021-11-24, FOUND=1
NOTICE 2005:  for returned 3, Josh, K, 2021-11-24, FOUND=2
DO

Thanks a lot @Jim_Knicely . We need to make a request to the production area for the upgrade.