CREATE FUNCTION (UDP) with SELECT and using CURSOR

josefp

Hi Vertica SME,

Is it possible to create a function in vertica something like this?

1. How to declare pre-defined variable inside the procedure and function?
2. Is it possible to make a cursor for SQL statement, and how?
3. 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;

sKwa

Hi!

Is it possible to make a cursor for SQL statement[...]?

Stored Procedures are not supported.
FYI: you can NOT do INSERT in 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?

marcothesane

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