CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.6
Joel
Joel

Reputation: 8948

Conditional CTE in PostgreSQL

I'm trying to write a dynamic query. I've tried to simplify this as best as I can. @Condition and @Filter are the dynamic inputs of the query.

There are two cases:

Pseudo code a little bit like this:

WITH selected_metadata AS (
    IF (@Condition = TRUE) {
        -- Do not filter on metadata
        SELECT NULL;
    }
    ELSE {
        -- Find the matching metadata rows
        SELECT id FROM metadata WHERE field = @Filter
    }
)
SELECT 
    *
FROM data
WHERE
    (
        -- Ignore the metadata filter
        selected_metadata IS NULL 
        OR
        -- Filter on metadata
        data.metadataid IN (selected_metadata)
    )
    ...more filters

I think there might be easier ways to do this without a CTE, but because of how the query looks in real life, I think I need to split it up to minimize complexity.

Upvotes: 1

Views: 2422

Answers (2)

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6289

Try something like this:

WITH selected_metadata AS (
  -- Find the matching metadata rows
  SELECT id FROM metadata WHERE field = @Filter OR @Condition <> TRUE
)
SELECT ...

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

I think you can try to set those condition in where instead of CTE directly.

SELECT 
    *
FROM data
WHERE
    (
        -- Ignore the metadata filter
        @Condition = TRUE
        OR
        -- Filter on metadata
        @Condition = FALSE AND data.metadataid IN (SELECT ID FROM selected_metadata AND field = @Filter)
    )
    ...more filters

Upvotes: 1

Related Questions