Coin Flip User Defined SQL Function

Posted July 2, 2019 by Jim Knicely, Vertica Principal Solution Architect

Hand with suit cuff flips shiny gold coin
In the past when my wife and I couldn’t decide which of two restaurants to go 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/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEFUNCTIONSQLFunctions.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOMINT.htm

Have fun!