SQL: Utilizing criteria table with different types of criteria applied to different fields

Question

I'm looking on how to provide permissions to users where I have a criteria table that has field/value pairs that match to different fields in my dataset.

Below is the permissions table.

Field	Value	Permissioned user
region	AMER	Kim
manager	Chris	Kim
division	Sales	Kim
region	EMEA	Julie
manager	Jim	Julie

This is the format of the data. Different fields represent the different fields above. There can be 8 or more different fields (eg region, business unit, dept head, etc) so I need an approach that scales beyond my dummy dataset.

EMP ID	region	division	manager
1	AMER	Marketing	Chris
2	AMER	Sales	Chris
3	EMEA	Sales	Chris
4	AMER	Sales	Jim
5	EMEA	Marketing	Jim
6	EMEA	Sales	Jim

The desired output applies the criteria across the different fields.

Permisioned User	EMP ID	region	division	manager
Kim	2	AMER	Sales	Chris
Julie	5	EMEA	Marketing	Jim
Julie	6	EMEA	Sales	Jim

I'm not really sure where to begin as I can't seem to get close. Intersect means that I always have the same fields applied in criteria but this isn't the case. Plus different users can have different number of criteria rules. Any help is really appreciated!

Thanks!

Answer 1

If the exhaustive list of fields that may be used to define permissions is fixed, then you can create a static query. If the list of fields is not fixed and only known at the run time, then you need a dynamic query.

Static list of permission fields / Static query

We cross join the permissions and data tables. Then, we group the rows by "Permissioned user" and "EMP ID", and we only keep the grouped rows which all satisfy the (field, value) correspondance thanks to the aggregate function bool_and in the HAVING clause. Finally we add all the columns of the data table and we order by "EMP ID" :

WITH list AS
(
SELECT p."Permissioned user"
     , d."EMP ID"
  FROM data AS d
 CROSS JOIN permissions AS p
 GROUP BY p."Permissioned user", d."EMP ID"
HAVING bool_and( CASE 
                   WHEN p.field = 'region' THEN p.value = d.region
                   WHEN p.field = 'manager' THEN p.value = d.manager
                   WHEN p.field = 'division' THEN p.value = d.division
                   WHEN [...]  -- insert here the rest of the fields list
                   ELSE FALSE
                 END
               )
)
SELECT l."Permissioned user", d.*
  FROM list AS l
 INNER JOIN data AS d
    ON d."EMP ID" = l."EMP ID"
 ORDER BY d."EMP ID" ;

Variable list of permission fields / Dynamic query

The dynamic query allows to search data according to criteria which are known only at the run time. It must be embedded in a plpgsql function which will return the result of the dynamic query. For each "Permissioned user" in table permissions, the principle is to generate the where_clause string which matches with the WHERE clause of the query searching in table data. The "Permissioned user" and the where_clause are passed to the plpgsql function permissioned_user which contains the dynamic query and returns the list of "EMP ID" in table data :

CREATE OR REPLACE FUNCTION permissioned_user 
( INOUT "Permissioned user" text
, IN where_clause text
, OUT "EMP ID" integer
)
RETURNS setof record LANGUAGE plpgsql IMMUTABLE AS
$$
BEGIN  
RETURN QUERY EXECUTE E'
    SELECT ' || quote_nullable("Permissioned user") || E'
         , "EMP ID"
      FROM data AS d
     WHERE ' || where_clause ;
END ;
$$

Then the following query returns the expected result. First it generates the where_clause string associated to every "Permissioned user" in table permissions. Then it call the function permissioned_user(), gets the "EMP ID" list and joins with the rest of the data columns :

WITH list AS
(   SELECT "Permissioned user"
         , string_agg('d.' || quote_ident(Field) || E' = \'' || quote_nullable(Value) || E'\'', ' AND ') AS where_clause
      FROM permissions
     GROUP BY "Permissioned user"
)
SELECT a."Permissioned user", d.*
  FROM list AS l
 CROSS JOIN LATERAL permissioned_user(l."Permissioned user", l.where_clause) AS a
 INNER JOIN data AS d
    ON d."EMP ID" = a."EMP ID"
  ORDER BY a."EMP ID"

Note that any Field value in table permissions which doesn't correspond to a column name in the data table will generate a SQL ERROR.