User-Defined Functions to the Rescue

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser, VerticaPartners

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, Registered User

    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 Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited May 9

    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, Registered User

    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!

Sign In or Register to comment.