Query result precedence if filter columns have values

Question

I have a table "customer_config" with these columns:

company (varchar)
warehouse (numeric)
section (numeric)
config_keyword (varchar)
config_value (varchar)

The two config_* columns can apply to an entire company (warehouse and section are null), an entire warehouse within a company (section is null), or a section within a warehouse.

So we could have a default row for the company, and then one or more rows that override a configuration value for a specific warehouse or warehouse & section.

I want to return only the most specific row for a given company, warehouse, and section. Something like this pseudocode:

results = select * from customer_config where (all match)
if results empty
    results = select * from customer_config where (company_code and warehouse match)
if results empty
    results = select * from customer_config where (company_code matches)

The most specific row shall take precedence.

Update

There can be multiple entries for the same config_keyword on the same level.
Is it also possible to return multiple rows for a single keyword?

Answer 1

"Only the most specific row"

While looking for a single result row (like your original question indicated):

This is a bit more verbose, but very clear and probably fastest if supported with an index on (company_code, warehouse, section) - like you should probably have anyway (depends on undisclosed info).

SELECT * FROM customer_config
WHERE  (company_code, warehouse, section) = ($1, $2, $3)

UNION ALL
SELECT * FROM customer_config
WHERE  (company_code, warehouse) = ($1, $2)

UNION ALL
SELECT * FROM customer_config
WHERE  company_code = $1
LIMIT  1;

Postgres will stop executing as soon as a row has been found. Test with EXPLAIN ANALYZE, you'll see "never executed" for remaining sub-SELECTs.

Note that LIMIT 1 applies to the whole query, not the last SELECT. (You'd need parentheses to change that.) Similar:

• How to force order of WHERE clauses in PostgreSQL?

"All rows for the most specific match"

If multiple rows can exist on each level.

Can be solved with pure SQL of course. For instance with a chain of CTEs. But this custom PL/pgSQL function should be more efficient:

CREATE OR REPLACE FUNCTION trade_volume (_company_code varchar, _warehouse numeric, _section numeric)
  RETURNS SETOF customer_config
  LANGUAGE plpgsql STABLE PARALLEL SAFE AS
$func$
BEGIN
   RETURN QUERY
   SELECT * FROM customer_config
   WHERE (company_code, warehouse, section) = ($1, $2, $3);

   IF FOUND THEN RETURN; END IF;
   
   RETURN QUERY
   SELECT * FROM customer_config
   WHERE (company_code, warehouse) = ($1, $2);

   IF FOUND THEN RETURN; END IF;
   
   RETURN QUERY
   SELECT * FROM customer_config
   WHERE  company_code = $1;
END
$func$;

Call:

SELECT * FROM trade_volume ('my_comany_code', 123456, 123);

Be sure to have the index mentioned above.

If the first query returns any rows, the function is done. The rest is not even planned. Etc.

Related:

• Return default rows from a function when first SELECT does not return rows
• Writing a view that caches to another table

I made the function PARALLEL SAFE to allow parallelism in Postgres 14 or later. (Only relevant for big tables.) Quoting the release notes:

Allow plpgsql's RETURN QUERY to execute its query using parallelism (Tom Lane)

Answer 2

This should work:

SELECT DISTINCT ON (config_keyword)
       config_keyword, config_value
FROM customer_config
WHERE company_code = $1
  AND coalesce(warehouse, $2) = $2
  AND coalesce(section, $3) = $3
ORDER BY config_keyword,
         section IS NULL,
         warehouse IS NULL;

DISTINCT ON will return the first row for each config_keyword, and results where section is not NULL will sort before results where section is NULL, because FALSE < TRUE. The same for warehouse.

Answer 3

I don't use PostgreSQL, but in oracle it can be done with analitic functions to return multiple rows for a single keyword:

select * 
  from (
     select rank() over (order by section, warehouse, company_code ) rn, 
            c.* 
       from customer_config c
      where company_code = $1
        and coalesce(warehouse, $2) = $2
        and coalesce(section, $3) = $3
       )
 where rn = 1;