Options
CREATE FUNCTION (UDP) with SELECT and using CURSOR
josefp
Vertica Customer ✭
Hi Vertica SME,
Is it possible to create a function in vertica something like this?
- How to declare pre-defined variable inside the procedure and function?
- Is it possible to make a cursor for SQL statement, and how?
- How to pass variables inside the function or procedure?
Code is something like this, below:
create or replace function (arg1 numeric) return numeric
declare
itr number;
name varchar (60);
emp_tbl CURSOR FOR
SELECT emp_id, full_name, dept_name
FROM employees;
begin
loop
fetch emp_tbl into name;
exit when not found;
v_stmt := 'insert into employees2 (emp_id, name, dept) select emp_id, '
end loop;
end;
0
Comments
Hi!
Stored Procedures are not supported.
FYI: you can NOT do
INSERTin UDFx, so you can not do it with UDFx too.PS: can you describe what are you trying to achieve and we will try to find a solution for you?
I agree with sKwa. In over 90% of the cases I saw, other DBMSs required cursor loops in order to be able to "portion" the data, often for multi-table joins that would otherwise cause the run time and memory requirements of a DML process to grow exponentially with the number of rows. So process 5000 rows at a time instead of all 3 million in one go.
Vertica can often make this altogether unnecessary. Worth trying that path.
Marco