Dynamic where condition PostgreSQL-CodePudding

I am building a CRUD application that allows the user to input some search criteria and get the documents corresponding to those criteria. Unfortunately i have some difficulties in creating a query in postgres that uses different conditions in the where part, based on the input sent by the user.

For example if the user set as search criteria only the document number the query would be defined like this:

select * from document where document_num = "value1"

On the other hand if the user gave two criteria the query would be set up like this:

select * from document where document_num = "value1" and reg_date = "value2"

How can i set up a query that is valid for all the cases? Looking in other threads i saw as a possible solution using coalesce in the where part:

document_num = coalesce("value1", document_num)

The problem with this approach is that when no value is provided postgres converts the condition to document_num IS NOT NULL which is not what i need (my goal is to set the condition to always true).

Thanks in advance

CodePudding user response：

If I understand correctly, you can try to pass the user input value by parameter.

parameter default value might design that the user can't pass if the user didn't want to use the parameter it will use the default value.

we can use OR to judge whether use parameter otherwise ignores that.

SELECT * 
FROM document 
WHERE (document_num = :value1 OR :value1 = [default value]) 
AND (reg_date = :value2 OR :value2 = [default value])

CodePudding user response：

So the solution by @D-shih will work if you have a default value and you can also use COALESCE as below.

SELECT *
FROM document
WHERE document_num = COALESCE("value1", default_value)
AND reg_date = COALESCE("value2", default_value);

If you don't have default values then you can create your query using CASE WHEN(here I am supposing you have some variables from which you will determine which conditions to apply like when to apply document_num or when to apply reg_date or when to apply both). Giving a little example below.

SELECT *
FROM document
WHERE
(
    CASE
        WHEN "value1" IS NOT NULL THEN document_num = "value1"
        ELSE TRUE
    END;
) 
AND (
    CASE
        WHEN "value2" IS NOT NULL THEN reg_date = "value2"
        ELSE TRUE
    END;
)

You can read more how to use CASE WHEN here.