0

I was writing a PostgreSQL function. In that function, I had 2 cases to be checked.

  1. Check if a value exists in the array given
  2. 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?

1 Answer 1

1

You need to use

A.inuri <> ALL (.... your array here ...)
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.