Avoid almost duplicating query in postgres plpgsql function

Question

Is there a better way to write this query in a plpgsql function than almost duplicating the query twice? Any way to say if valB is null then it should match anything in colB, essentially being removed from the where clause?

if (valB is not null) then
    update mytable set colA = valA where (colB, colC) = (valB, valC);
else
    update mytable set colA = valA where (colC) = (valC);
end if;

user330315 · Accepted Answer · 2019-03-24 16:29:56Z

1

You can use an OR condition with the parameter:

update mytable 
   set colA = valA 
where (valb is null and colC = valC)
   or (valb is not null and (colb, colc) = (valb, valc));

answered Mar 24, 2019 at 16:29

user330315

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

Comments

Gordon Linoff · Accepted Answer · 2019-03-24 16:41:41Z

1

You can use or. I would phrase this as:

update mytable 
   set colA = valA 
where colC = valC and
       (valb is null or colb = valb);

answered Mar 24, 2019 at 16:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

user779159 Over a year ago

Would you say there's any practical difference between what you suggested and modifying it slightly, replacing (valb is null or colb = valb) with (colb = case when valb is null then colb else valb end) or are they identical? Does the other way have any downsides in terms of performance or anything else (apart from being a bit longer to read)?

Gordon Linoff Over a year ago

@user779159 . . . I prefer leaving case expressions out of where clauses.

Collectives™ on Stack Overflow

Avoid almost duplicating query in postgres plpgsql function

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related