Custom function that calls a table in the body

BikashNayak

Vertica is not supporting UDF that calls a table within it . Can anyone help me finding an alternate to this . Most of out reporting queries calls business functions . This is a showstopper for us .

SruthiA

@BikashNayak Could you please share sample example on what you are trying... what is the error you are receiving?

BikashNayak

sample Oracle function example below. I need to create the same in vertica .

CREATE OR REPLACE FUNCTION REPORT_NET.GET_WORKCENTER_ID
(V_WORKCENTER_NAME VARCHAR2)

RETURN NUMBER
IS
V_WORKCENTER_ID NUMBER;
BEGIN

SELECT WC.WORKCENTER_ID
INTO V_WORKCENTER_ID
FROM WC1.WORKCENTER WC
WHERE WC.WORKCENTER_NAME =V_WORKCENTER_NAME;

RETURN V_WORKCENTER_ID;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END;
/

Bryan_H

Vertica CREATE FUNCTION doesn't support SELECT currently. Some options:
Current Vertica versions support OUT and INOUT for stored procedures, so you may write a stored procedure that executes the function and returns the result.
You could also write a Python scalar function that executes a SQL query and returns the value (or NULL), though this requires a SQL connection and login credential in the UDSF.

VValdar

Hi BikashNayak,

Business Functions at the reporting level doesn't scale very well with increasing volume.
With Vertica you want to implement the business logic either in a view or better while inserting into a table.