Select with list as parameter and if it is empty select with no parameter in PostgreSQL

Question

I have a PostgreSQL database with a food table and a tag table. I need to query data from a Python GUI based on the tag(s) using psycopg3. If the tag argument is an empty list, I need to use data from all of the restaurants in the food table.

The query below returns the desired result if some of the restaurant(s) with argument id(s) ('ids_arg') have some of the argument tag(s) ('tags_arg'). How can I query and compute the data from all the restaurants regradless of ids or tags if the 'tags_arg' list is empty, resulting in t.name = ANY (array[]::text[]) in PostgreSQL? I can get the desired result by adding a UNION ALL with the same query without the INNER JOIN and using the first or second row of the result table depending on the results but that approximately doubles the lines in the query string file. It matters considering readability. Is there a neat way of achieving the same result without the UNION ALL or otherwise adding a significant amount of lines? Efficiency analysis would also be appreciated.

SELECT Total, Total - Drinks AS "Without drinks", Drinks   
FROM (
  SELECT 
  SUM (f.prepared) 
  FILTER (
    WHERE f.date BETWEEN 'first' AND 'last'
    AND f.restaurant_id = ANY ('ids_arg')
  ) AS Total,
  SUM (f.prepared)
  FILTER (
    WHERE (f.product_id = 10 OR f.product_id = 17) 
    AND f.date BETWEEN 'first' AND 'last'
    AND f.restaurant_id = ANY ('ids_arg')
  ) AS Drinks
  FROM food f
  INNER JOIN tags t 
  ON t.restaurant_id = f.restaurant_id 
  WHERE t.name = ANY ('tags_arg'::text[])
);

EDIT: here is a dbfiddle with the UNION ALL queries and Zegarek's answer queries.

Answer 1

Add an adequate condition to your where:

SELECT Total, Total - Drinks AS "Without drinks", Drinks   
FROM (
  SELECT 
  SUM (f.prepared) 
  FILTER (
    WHERE f.date BETWEEN 'first' AND 'last'
    AND f.restaurant_id = ANY ('ids_arg')
  ) AS Total,
  SUM (f.prepared)
  FILTER (
    WHERE (f.product_id = 10 OR f.product_id = 17) 
    AND f.date BETWEEN 'first' AND 'last'
    AND f.restaurant_id = ANY ('ids_arg')
  ) AS Drinks
  FROM food f
  INNER JOIN tags t 
  ON t.restaurant_id = f.restaurant_id 
  WHERE array_position('tags_arg'::text[],t.name) IS NOT null 
     OR array_length('tags_arg'::text[],1) IS null
);

This also makes it null-safe. A regular any(text[]) yields a null regardless of whether your t.name is in the tags_arg array or not, as long as there's one or more nulls also present in the array, or t.name itself is a null, or both. That's because any() uses regular equality = operator, while array_position() uses is not distinct from construct.

Here's a cheatsheet: demo at db<>fiddle

select tags_arg,
       tags_arg::text[] as_array,
       'tag1'=any(tags_arg::text[])                                 as "base",
       'tag1'=any(coalesce(tags_arg::text[],array['tag1']::text[])) as "+coalesce()",
       'tag1'=any(coalesce(tags_arg::text[],array['tag1']::text[]))
            OR array_length(tags_arg::text[],1) is null             as "+coalesce()+OR",
       'tag1'=any(coalesce(tags_arg::text[],array['tag1']::text[]))
            OR array_length(tags_arg::text[],1) is null
            OR array_position(tags_arg::text[],null) is not null    as "+coalesce()+OR+nullsafe",
       array_position(tags_arg::text[],'tag1') is not null 
            OR array_length(tags_arg::text[],1) is null             as "final"
from (values  ('{tag1,tag2,tag3}') --matching array
             ,('{tag4,tag5}')      --non-matching array
             ,('{tag6,null}')      --non-matching with a null
             ,('{null}')           --only a null
             ,('{}')               --empty
             ,(null)              ) as args(tags_arg);

tags_arg	as_array	base	+coalesce()	+coalesce()+OR	+coalesce()+OR+nullsafe	final
{tag1,tag2,tag3}	{tag1,tag2,tag3}	T	T	T	T	T
{tag4,tag5}	{tag4,tag5}	f	f	f	f	f
{tag6,null}	{tag6,NULL}	null	null	null	T	f
{null}	{NULL}	null	null	null	T	f
{}	{}	f	f	T	T	T
null	null	null	T	T	T	T