I was writing a PostgreSQL function. In that function, I had 2 cases to be checked.
- Check if a value exists in the array given
- Check if a value does not exist in an array
below are the queries I'm trying
For the first case
SELECT * FROM TableA A
INNER JOIN TableB B on A.inuri = B.resource_uri
INNER JOIN TableI I on I.resource_id = B.resource_id
WHERE B.resource_type like '%%' AND
outuri='./a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)' AND (("isDeleted"='true')) OR A.inuri = ANY
(
'{./a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6),
./a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)/d(f0576f89-1e0e-4eda-b498-0976f3e19c5c),
./a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)/d(baf1d782-77f8-4372-9601-47a486f0700a)}'
)
and for the second case
SELECT * FROM TableA A
INNER JOIN TableB B on A.inuri = B.resource_uri
INNER JOIN TableI I on I.resource_id = B.resource_id
WHERE B.resource_type like '%%' AND
outuri='./a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)' AND (("isDeleted"='false')) AND A.inuri NOT IN
(
'{./a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6),
./a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)/d(f0576f89-1e0e-4eda-b498-0976f3e19c5c),
./a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)/d(baf1d782-77f8-4372-9601-47a486f0700a)}'
)
The first case works without any issue. But for the second case, it won't work when multiple values are passed.
Eg: if I give
A.inuri NOT IN
(
'{./a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)/d(f0576f89-1e0e-4eda-b498-0976f3e19c5c)}'
)
I will get all except ./a/b(d91dae45-9e10-43c5-bf08-f52ec80732d3)/c(ee0cc326-fbaf-4d04-a9a6-31d515dea1f6)/d(f0576f89-1e0e-4eda-b498-0976f3e19c5c). But if I give 3 of them it is not validating anything. I will get all 3 items listed in the NOT IN Clause
How can I fix this?