How to make a conditional query in Postgres function

Question

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.

I'm not sure of what would happen if p_id_3 is null for example, can only ensure that p_id_1 will be come always. — Ezequiel Miranda
– Ezequiel Miranda, Commented Sep 25, 2015 at 21:17

klin · Accepted Answer · 2015-09-25 21:26:42Z

1

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.

answered Sep 25, 2015 at 21:26

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to make a conditional query in Postgres function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related