can we create any function , cursor, or procedure in dremio . I have a requirement to create a procedure or function , cursor based on some conditions I want to add values to those columns .
There are a couple options available today
- Being open-source, Dremio allows the capabilities for users to create their own UDF (in Java). This will appear as a SQL function in the UI.
- While stored procedure’s aren’t directly supported today, a similar workaround is by creating VDSs and chaining them together. In other words, you would create a VDS with a logic you are trying to reuse, save it, secure it, and give users the ability to query and build upon it.
Can you provide a simple example? For example, I have SPs that use variables then DML language – are you suggesting we need to write a UDF with that embedded then rather than ‘call’ the SP, invoke it as a function?
REPLACE PROCEDURE xxx
CALL vds (parameters?);
more vsp dml statements? insert into xxx; update xxx;
REPLACE PROCEDURE vds (x,y,z)
DECLARE USER_CNT INTEGER;
DECLARE CURR_TS TIMESTAMP;
SEL Current_Timestamp INTO CURR_TS; SELECT Count(*) INTO USER_CNT FROM ...; IF USER_CNT = 0 THEN LEAVE PROCESS_USER; END IF; FOR CUR_PTR AS MYPTR CURSOR FOR SELECT xxx DO UPDATE x INSERT INTO x END FOR;