Last active
June 16, 2021 05:47
-
-
Save sloppycoder/43a30372f22257acd0ab125b35130b59 to your computer and use it in GitHub Desktop.
Delay a query in PostgreSQL
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| drop table if exists dummy_data; | |
| create table dummy_data (index int, delay decimal, account_name varchar); | |
| insert into dummy_data | |
| values | |
| (1, 0.01, 'ACCOUNT1'), | |
| (2, 0.1, 'ACCOUNT2'), | |
| (3, 0.5, 'ACCOUNT3'), | |
| (4, 1, 'ACCOUNT4'), | |
| (5, 5, 'ACCOUNT5'); | |
| -- create a function that returns the index of a random row | |
| create or replace function random_index(account_num varchar) | |
| returns int | |
| language plpgsql | |
| as | |
| $$ | |
| declare | |
| rand int; | |
| rows int; | |
| begin | |
| select count(*) into rows from dummy_data; | |
| select round(random()*rows)+1 into rand; | |
| return rand; | |
| end; | |
| $$ | |
| -- select data using random index | |
| -- and pg_sleep on the delay value returned | |
| select index, delay, account_name, pg_sleep(delay) from dummy_data where index = select random_index('aBc'); | |
| -- the version below some times return multiple rows, why?! | |
| select index, delay, account_name, pg_sleep(delay) from dummy_data where index = random_index('aBc'); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment