CODEWITHSUNDEEP
postgresqlplpgsql
Ezequiel Miranda
Ezequiel Miranda

Reputation: 345

How to make a conditional query in Postgres function

I'm starting with Postgress, and I need to make a query with four ids as params, these can be NULL, only the first is mandatory, so I'm trying to get a data list with data of every id. How can I check if id param IS NOT NULL inside a WHERE condition. I'll give you an example (not working)

CREATE OR REPLACE FUNCTION sp_lock(
    IN p_id_1 character varying, 
    IN p_id_2 character varying DEFAULT NULL, 
    IN p_id_3 character varying DEFAULT NULL, 
    IN p_id_4 character varying DEFAULT NULL)
RETURNS SETOF character varying AS
$BODY$
BEGIN 
    UPDATE stock
    SET lock = 1
    FROM  (
      SELECT s.id
      FROM  stock s
      WHERE
        (ls.id = p_id_1 or p_id_1 is null) OR
        (ls.id = p_id_2 or p_id_2 is null) OR
        (ls.id = p_id_3 or p_id_3 is null) OR
        (ls.id = p_id_4 or p_id_4 is null) AND
        ls.lock = 0
      FOR UPDATE OF s
    ) i
    WHERE i.id = stock.id;

Here I need to check first, if the param IS NOT NULL, and then concatenate to the condition, with OR exp.

Upvotes: 1

Views: 1226

Answers (1)

klin
klin

Reputation: 121574

Your can use in operator in the where condition 

WHERE 
    s.id in (p_id_1, p_id_2, p_id_3, p_id_4) AND
    s.lock = 0;

Btw, you do not need a subquery:

UPDATE stock
SET lock = 1
WHERE
    id in (p_id_1, p_id_2, p_id_3, p_id_4) AND
    lock = 0;

does exactly the same but quicker.

Upvotes: 1

Related Questions