0

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.

4
  • May I ask what you want to use this for?
    – Philᵀᴹ
    Nov 14, 2018 at 0:43
  • With everything completely dynamic and no limits, there are various pitfalls to steer clear of. Typically, there are more confining specs ... Here is a detailed related answer - from 2013: stackoverflow.com/a/17355733/939860 Nov 14, 2018 at 1:05
  • @Philᵀᴹ - I've tried to make that clearer by adding more context to my question.
    – mg1075
    Nov 14, 2018 at 4:24
  • @ErwinBrandstetter - I need to account for at minimum about 70 different measures, and each of these measures can have different equality operators applied to them. And, optimally, I want the end user to be able to apply more than one condition on a measure. I'm worried the complexity of such a query, although using dynamic sql as e.g. here - stackoverflow.com/a/12047277/538962 - would be a maintenance headache.
    – mg1075
    Nov 14, 2018 at 4:32

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.