Return values contained in given array - or all values if no match

sqldoug 0
Viewed 42

Given an array literal as a parameter, and a table expression as (pseudo-code-like):

SELECT y.id
  FROM (VALUES (4)
             , (1)
             , (2)
             , (6)) y (id)
WHERE y.id = < if ANY('{4}') then 4 else y.id >
;

Is there a WHERE clause that can give y.id 4 if there's a match in the array, or every y.id if not, resulting in e.g.,

id
--
4

or

id
--
4
1
2
6

If the array literal did not contain 4.

Need to be compatible with Postgres version 12.

sql postgresql
1 Answers

To implement Bergi's clever suggestion, you need to wrap the set in a CTE to reference it twice. And provide the array twice:

WITH y(id) AS (
   VALUES
     (4)
   , (1)
   , (2)
   , (6)
   )
SELECT id
FROM   y
WHERE  y.id = ANY('{4}')  -- provide array here
   OR  NOT EXISTS (SELECT * FROM y WHERE y.id = ANY('{4}'));  -- and here

Or this variant with another CTE, providing the array just once:

WITH y(id) AS (
   VALUES
     (4)
   , (1)
   , (2)
   , (6)
   )
, match AS (
   SELECT * FROM y
   WHERE  id = ANY('{4}')  -- array here
   )
SELECT * FROM match
UNION ALL
SELECT * FROM y
WHERE  NOT EXISTS (SELECT FROM match);

If your source of rows is an actual table either gets simpler.

If performance is crucial, consider a PL/pgSQL function:

CREATE OR REPLACE FUNCTION func(_arr int[])
  RETURNS TABLE (id int)
  LANGUAGE plpgsql STABLE AS
$func$
BEGIN
   RETURN QUERY
   SELECT y.id
   FROM   y
   WHERE  y.id = ANY(_arr);
   
   IF NOT FOUND THEN
      RETURN QUERY
      SELECT y.id
      FROM   y;
   END IF;
END
$func$;

PL/pgSQL builds up the return-set by simply appending to it. The special variable FOUND shows if the last SQL command returned any rows. See:

So you don't need another filter condition, and the query plan(s) can be simpler. Should be faster. Especially if we find matches most of the time and return after the first query. (PL/pgSQL works with prepared statements internally, which may or may not be beneficial on top ...)

If you return big sets, the function might be slower again, because of the way it builds up the set before returning the complete result ...

Erwin Brandstetter0
Related