CREATE FUNCTION (UDP) with SELECT and using CURSOR

josefpjosefp Registered User

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;

Comments

  • sKwasKwa Registered User

    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?

  • marcothesanemarcothesane Employee, Registered User

    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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file