Postgresql – postgres dyanmic and unlimited where conditions

dynamic-sqlpostgresql

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.

Best Answer

Have the function take a structure that defines the constraint to apply and parse that structure into a SQL where expression, then execute it.

The structure could be represented as JSON, XML, or any other type that can be handled by postgresql built-ins or your code.

For safety aghainst injection attacks be sure to not copy any raw data from the struct to the query, all values and identifiers should be quoted (quote_identifier(), quote_nullable(), etc...) and all operators should come from string literals in the function code.

the function itself would be structured like something like.

CREATE OR REPLACE FUNCTION filter( conditions text ) 
   RETURNS SETOF sometable 
   LANGUAGE plpgsql 
   AS $$
        DECLARE 
          whereclause text := '';
        BEGIN
          whereclause = whereclause || conditions 
          RETURN QUERY EXECUTE 
             'SELECT * FROM sometable WHERE ' || whereclause;
        END;
      $$;

but probably with some more involved processing of conditions before the RETURN QUERY EXECUTE

Related Question