Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

CREATE FUNCTION (UDP) with SELECT and using CURSOR

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

  • 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?

  • 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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.