postgresql - 根据多个参数搜索 postgres 表但不需要所有参数

Question

我正在尝试创建一个查询，根据 4 个值搜索 1 个表。这些值都不是必需的，它应该基于所有这些值返回。

如果始终提供所有值，下面是我将如何做。

CREATE OR REPLACE FUNCTION "GetValues"(
  "@value1"                TEXT,
  "@value2"                TEXT,
  "@value3"                TEXT
  "@value4"                TEXT
)
RETURNS TABLE(
  "id"                     UUID,
  "@value1"                TEXT,
  "@value2"                TEXT,
  "@value3"                TEXT
  "@value4"                TEXT  
) AS
$func$
BEGIN
  RETURN QUERY SELECT
    c."id",
    c."value1",
    c."value2",
    c."value3",
    c."value4"
  FROM "values" AS v
  WHERE "value1" = "@value1"
  AND "value2" = "@value2"
  AND "value3" = "@value3"
  AND "value4" = "@value4";
END;
$func$ LANGUAGE PLPGSQL;

下面是我想象中的做法，但我无法在其中进行任何类似的工作。

IF ("@value1" IS NOT NULL) THEN
  WHERE "value1" = "@value1"
END IF;

我是否应该做很多单独的 SELECTS，然后将它们与 UNION 结合起来？我认为这行不通，因为我只希望显示符合所有条件的内容？

Answer 1

使用COALESCE():

SELECT
    "id",
    "value1",
    "value2",
    "value3",
    "value4"
FROM "values"
WHERE "value1" = COALESCE("@value1", "value1")
AND "value2" = COALESCE("@value2", "value2")
AND "value3" = COALESCE("@value3", "value3")
AND "value4" = COALESCE("@value4", "value4")

Working example in rextester.

不相关但很重要。在双引号中使用表名或列名绝对不会让生活更轻松。表的名称“值”可能被认为是一种不好的做法。