User-Defined Functions to the Rescue

[Deleted User]

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!

ScottL

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_Knicely

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

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?

ScottL

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!

aryehgielchinsky

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_Knicely

@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)