Custom function that calls a table in the body

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 .

Answers

  • SruthiASruthiA Vertica Employee Administrator

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

  • 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_HBryan_H Vertica Employee Administrator

    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.

  • VValdarVValdar Vertica Employee Employee

    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.

Leave a Comment

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