POSTGRESQL_FUNCTIONS.md (1288B)
1 # Notes on PostgreSQL Functions 2 3 ## Reduction of conditional WHERE statement 4 5 The WHERE statement for the function `distinct_startlist` was reduced as follows. 6 * Source: https://stackoverflow.com/a/73452497 7 8 Step 1, function with conditional where (CASE): 9 ```sql 10 where (case when publicOnly = true 11 then -- return public heats only for unauthenticated users 12 s.heat = any(heat_ids) and 13 h.private = false 14 else -- return all heats for authenticated users 15 s.heat = any(heat_ids) 16 end); 17 ``` 18 19 Step 2: 20 ```sql 21 where s.heat = any(heat_ids) and 22 (case when publicOnly = true then h.private = false else true end); 23 ``` 24 25 Step 3: 26 ```sql 27 where s.heat = any(heat_ids) and 28 (publicOnly = false or h.private = false); 29 ``` 30 31 Truth table: 32 33 | "Private" selected? | Description | Input variables | Result | 34 |---------------------|---------------|------------------------------------|--------------| 35 | yes | private heat | publicOnly=true & h.private=true | not selected | 36 | no | public heat | publicOnly=true & h.private=false | selected | 37 | yes | private heat | publicOnly=false & h.private=true | selected | 38 | no | public heat | publicOnly=false & h.private=false | selected | 39 ```