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.
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 ...
SELECT FROM without *, is there a difference? (I only remember not using SELECT 1 in postgres)
EXISTS expression which only cares for the existence of rows. An empty SELECT list is shortest & cheapest there. See: stackoverflow.com/a/19364694/939860 (Other RDBMS may not be allow an empty SELECT list.)
WHERE y.id = ANY('{4}') OR NOT EXISTS (SELECT * FROM y WHERE y.id = ANY('{4}'))?VALUESexpression 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.