Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Coin Flip User Defined SQL Function

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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.