我正试图创建一个查询,在其中我根据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;
我是不是应该做很多个人选择,然后把它们和工会结合起来?我不认为那会起作用,因为我只想看到符合所有标准的东西?
最佳答案
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.
不相关但很重要。在双引号中使用表名或列名肯定不会使生活更轻松。表的名称“values”可能被认为是一种不好的做法。
关于postgresql - 根据多个参数搜索postgres表,但不需要全部,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/52174589/