Simplify PostgreSQL function

Question

I have a PostgreSQL stored procedure that contains the following code:

IF something = TRUE THEN
    SELECT id INTO some_id FROM some_table WHERE some conditions LIMIT 1;
    RETURN QUERY SELECT * FROM some_table WHERE some conditions LIMIT 1;
ELSE
    SELECT id INTO some_id FROM some_table WHERE some OTHER conditions LIMIT 1;
    RETURN QUERY SELECT * FROM some_table WHERE some OTHER conditions LIMIT 1;
END IF;

DELETE FROM some_table where id = some_id;

Is there a way to simplify the above code? I guess there is nothing we can do about the repeated code in the IF and in the ELSE, but is there a way to avoid having 2 SELECT's every time? Is it possible to insert something in some_id while RETURN QUERY?

You can use RETURN QUERY EXECUTE ... in newer versions and do it with a dynamic SQL string created with format and string concatenation. — Craig Ringer
– Craig Ringer, Commented Sep 25, 2014 at 14:12
Your DELETE statement never gets executed, because both of your IF branches has a RETURN statement. — pozs
– pozs, Commented Sep 25, 2014 at 17:43
@pozs: No. RETURN QUERY does not exit from the function (unlike RETURN). — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 25, 2014 at 18:11
@CraigRinger: No need for EXECUTE here. Nothing dynamic, just two cases. — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 25, 2014 at 18:14
@ErwinBrandstetter of course, my bad. That's just the silly syntax highlight, which does not emphasise QUERY -- RETURN QUERY really does not exit. — pozs
– pozs, Commented Sep 25, 2014 at 18:21

Clodoaldo Neto · Accepted Answer · 2014-09-25 18:23:01Z

3

If the function does only what you posted then it is not necessary:

delete from some_table
where 
    something and (some conditions)
    or
    something is not true and (some other conditions)
returning *

edited Sep 25, 2014 at 18:23

answered Sep 25, 2014 at 14:14

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

Erwin Brandstetter Over a year ago

Missed the something IS NULL case. If something can be NULL, then use or something IS NOT TRUE for the 2nd expression to do the same as the IF statement above (or OP needs to re-examine what to do for TRUE / FALSE / NULL).

Collectives™ on Stack Overflow

Simplify PostgreSQL function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related