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

Question

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.

Sounds like you want `WHERE y.id = ANY('{4}') OR NOT EXISTS (SELECT * FROM y WHERE y.id = ANY('{4}'))`? — Bergi, Sep 22 '22 at 23:31
Is your actual source for the set a table or a freehand `VALUES` expression like in the example? (Or another array)? Can there be NULL values involved? How to deal with those? Show the exact table definition and which columns you want to return. Rough cardinalities of table and array? Indexes? It matters. — Erwin Brandstetter, Sep 23 '22 at 00:10
Thanks, the actual source is a table so these are good to consider. The suggestion from @Bergi applies well to that also. — sqldoug, Sep 23 '22 at 15:51

Erwin Brandstetter · Accepted Answer · 2022-09-23T21:38:11.030

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:

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 the complete result ...

Oh I didn't know you could `SELECT FROM` without `*`, is there a difference? (I only remember [not using `SELECT 1`](https://stackoverflow.com/q/6137433/1048572) in postgres) — Bergi, Sep 23 '22 at 17:12
@Bergi: Oh, both are very different. But it doesn't matter for an `EXISTS` expression which only cares for the existence of rows. An empty `SELECT` list is shortest & cheapest there. See: https://stackoverflow.com/a/19364694/939860 (Other RDBMS may not be allow an empty `SELECT` list.) — Erwin Brandstetter, Sep 23 '22 at 21:35

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

1 Answers1