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

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

id
--
4
1
2
6

If the array literal did not contain 4.

Need to be compatible with Postgres version 12.

CodePudding user response：

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. You can check the special variable FOUND if the last SQL command returned any rows. See:

Why is IS NOT NULL false when checking a row type?
Return default rows from a function when first SELECT does not return rows (also similar to your use case!)

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 all at once ...

CodePudding user response：

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