How would one properly write a function in Postgres to return a table based on a query where the query WHERE
clause can have both an unknown number of filter conditions, and the conditions themselves are dynamic?
I feel like this should be a solved problem, and maybe it is, but I am unable to find any examples in Postgres.
The type of conditions in involved in building one filter condition would be dynamically setting AND
, OR
, NOT
conditions, =
, >
, <
, >=
, <=
, etc. for a given column's values.
Then multiply this by an unknown n number of possible filter conditions.
In my scenario at the moment, there are nearly 70 different numerical measures which could be used to compose the where clause. Again, each of these measures could have AND
, OR
, NOT
conditions, =
, >
, <
, >=
, <=
, etc. applied. Moreover, each one of these 70 different numerical measures could be used more than once if different equality operators and low/high limit ranges are applied. At a 30,000 ft view, think of the problem as passing all the conditions you could generate in a tool like this to a Postgres query. Essentially you would be translating into a Postgres query the results of a web app end-user who is building a cohort from visual tools with 70+ measures at his/her disposal.
What is the best way to go about this in Postgres? Pass a json array of objects as a function parameter, convert them to a table, and base the dynamic where in some manner off of that table? Run a LOOP
based off the conditions? Other? Appreciate any help getting un-stuck on this challenge.