Coin Flip User Defined SQL Function

Jim_Knicely · July 2019

In the past when my wife and I couldn’t decide between which of two restaurants to grab dinner, we opened the Coin Flip app on one of our phones to let it decide.

Now I let Vertica choose for us via a simple User Defined SQL Function!

Example:

dbadmin=> CREATE OR REPLACE FUNCTION coin_flip() RETURN VARCHAR(5)
dbadmin-> AS
dbadmin-> BEGIN
dbadmin->    RETURN CASE RANDOMINT(2)
dbadmin->             WHEN 0 THEN 'HEADS'
dbadmin->             WHEN 1 THEN 'TAILS'
dbadmin->           END;
dbadmin-> END;
CREATE FUNCTION

dbadmin=> SELECT coin_flip, COUNT(*) FROM (SELECT coin_flip() AS coin_flip FROM big_table LIMIT 100000) foo GROUP BY coin_flip;
coin_flip | COUNT
-----------+-------
TAILS     | 49899
HEADS     | 50101
(2 rows)

dbadmin=> SELECT coin_flip, COUNT(*) FROM (SELECT coin_flip() AS coin_flip FROM big_table LIMIT 100000) foo GROUP BY coin_flip;
coin_flip | COUNT
-----------+-------
TAILS     | 49848
HEADS     | 50152
(2 rows)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEFUNCTIONSQLFunctions.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOMINT.htm

Have fun!