User-Defined Functions to the Rescue

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

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?

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.

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