Precise question.
Table ROW
value1 a
value2 b
value3 null
value4 d
Function parameters
CREATE OR REPLACE FUNCTION "GetValues"(
"@value1" VARCHAR(50),
"@value2" VARCHAR(50),
"@value3" VARCHAR(50),
"@value4" VARCHAR(50)
)
BEGIN
RETURN QUERY SELECT(
t."value1",
t."value2",
t."value3",
t."value4",
)
FROM "table" as t
WHERE t."value1" = COALESCE("@value1", c."value1")
AND t."value2" = COALESCE("@value2", c."value2")
AND t."value3" = COALESCE("@value3", c."value3")
AND t."value4" = COALESCE("@value4", c."value4");
END;
If I use the above function and only provide the following:
('a', null, null, 'd')
It will return [] even if 'a' and 'd' are found and I found that this only happens if I provide a parameter to search for something that is null and the value of the row is also null.
OLD DESCRIPTION BELOW
I have setup a get which uses COALESCE successfully to search by multiple or 1 parameter(s). However, if any one of those params that are not provided (so default to NULL) are actually NULL in the db because I haven't updated that field before, then it will always return an empty array, even though one of the provided params will successful match to a row in the table.
I just want to know if I need a new system all together to complete this search or if it is just an unfortunate effect of COALESCE?
Below is the relevant snippet.
FROM "table" as t
WHERE t."value1" = COALESCE("@value1", c."value1")
AND t."value2" = COALESCE("@value2", c."value2")
AND t."value3" = COALESCE("@value3", c."value3")
AND t."value4" = COALESCE("@value4", c."value4");
In the above, if I provide value1 and it matches but value4 is NULL in that row, then it will return [].
The return is a table with each of those 4 values.
You could do it like this:
FROM test as t
WHERE ("@value1" IS NULL OR t."value1" = "@value1")
AND ("@value2" IS NULL OR t."value2" = "@value2")
AND ("@value3" IS NULL OR t."value3" = "@value3")
AND ("@value4" IS NULL OR t."value4" = "@value4");