User-Defined Functions to the Rescue

Jim Knicely authored this tip.

A friend recently asked me why we don’t have certain functions available in Vertica that are offered in databases like MySQL. I shrugged it off because we have bigger fish to fry. But he kept pushing, so I showed him how easy it is to implement a user-defined function in Vertica to mimic his simple MySQL function.

Example:

dbadmin=> CREATE OR REPLACE FUNCTION find_in_set(x VARCHAR, y VARCHAR) RETURN INT
dbadmin-> AS
dbadmin-> BEGIN
dbadmin->   RETURN regexp_count(split_part(y, x, 1), ',') + 1;
dbadmin-> END;
CREATE FUNCTION

dbadmin=> SELECT find_in_set('90', '10,100,90,900,90');
find_in_set
-------------
           3
(1 row)

dbadmin=> SELECT find_in_set('ank', 'b,ank,of,monk');
find_in_set
-------------
           2
(1 row)

Have fun!

Comments

  • ScottLScottL Employee

    Hi Jim,

    Question: Why doesn’t Vertica have an option to create function using DML statements? Suppose I want to create a function to get the sequence name for provided table_name:

    CREATE or REPLACE FUNCTION UA3_PIUM.GetSequenceName(tblname varchar) RETURN varchar
    AS BEGIN
    RETURN (
    Select sequence_name from sequences where identity_table_name = : tblname );
    END;

    This will not work in Vertica; so we’d have to create a UDF which is far more work to develop and maintain.

    Thanks
    Scott

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited May 2018

    From my understanding, "Stored Procedures" were referred to as an "OMDB" feature... "Over Mike (Stonebraker's) Dead Body" :p

    As you've probably seen, you'll get a "ROLLBACK 4257: Only simple "RETURN expression" is allowed" message if you try to create a User Defined SQL Function that contains any of the following... "FROM, WHERE, GROUP BY, ORDER BY, LIMIT, aggregation, analytics, and meta function".

    I think that engineering determined that having the ability to run SELECT statements or DML in a User Defined SQL Function would cause a performance nightmare in the DB.

    What's wrong with just running the SQL? Why are you trying to encapsulate it in a function?

  • ScottLScottL Employee

    To answer your question: what's wrong with just running the SQL? Why are you trying to encapsulate it in a function?

    Reusability and simplicity. Why copy and include the same SQL query again and again if you can create a function, pass the table name and get the value back. Laziness is one of the developers three virtues! :)

    BTW, we really appreciate your team's Vertica tips - have been a lot of help to us! Keep them coming!

  • I think this only works if the search value is in the list. If it isn't this function returns the length of the list. I think you want it to return zero, or maybe null.

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited July 2019

    @aryehgielchinsky - Thanks for the feedback!

    See if this version works a little better :)

    CREATE OR REPLACE FUNCTION find_in_set(x VARCHAR, y VARCHAR) RETURN INT
    AS
    BEGIN
      RETURN REGEXP_COUNT(CASE
                            WHEN SPLIT_PART(y, ',', 1) = x THEN x
                            WHEN INSTR(y, ',' || x || ',') > 0 THEN LEFT(y, INSTR(y, x))
                            WHEN y ILIKE '%,' || x THEN y
                            WHEN SPLIT_PART(y, ',' || x || ',', 1) = y THEN NULL
                          END, ',') + 1;
    
    END;
    
    dbadmin=> SELECT find_in_set('90', '10,100,90,900,90');
     find_in_set
    -------------
               3
    (1 row)
    
    dbadmin=> SELECT find_in_set('ank', 'b,ank,of,monk');
     find_in_set
    -------------
               2
    (1 row)
    
    dbadmin=> SELECT find_in_set('1', '10,100,90,900,90');
     find_in_set
    -------------
    
    (1 row)
    
    dbadmin=> SELECT find_in_set('foo', 'b,ank,of,monk');
     find_in_set
    -------------
    
    (1 row)
    
Sign In or Register to comment.