I want to embed certain
DELETE statements into a function, to enhance re-usability. These functions have in common that the are manipulating a single row (if it exists).
An example is the following function:
create function my_table_delete(_identifier int) returns setof int as $BODY$ begin return query delete from my_table where identifier = _identifier returning identifier; end $BODY$ language plpgsql volatile strict;
This function accepts an identifier, deletes the corresponding row and returns the same identifier in case the delete was successful.
I am quite unsure if I should implement this as a set-returning function (like I did above) or a scalar function.
My reasoning for preferring set-returning function is the following:
In case of a scalar function, we will have a
NULLreturn value in case the row could not be found (while we will have 0 rows in case of the set-returning function). Having to deal with
NULLs is a pain.
I'm working with sets (SQL is all about sets), so it makes sense to just keep doing that.
Also, I prefer returning the identifier (
1 row: successful,
0 rows: not successful) instead of a
false: not successful), as it would allow constructs such as:
with d(id) as ( select * from my_table_delete(1234) ) insert into log_table(id, action) select d.id, 'DELETE' from d;
If I were to return a
boolean, the statement above could become less elegant.
I'm interested whether those arguments make sense. I'm quite new to the embedding logic into the database, so I'm not sure if I am addressing this correctly.
What do you think?
On a slightly related note, I also wonder if I should define the function as following:
create function my_table_delete(_identifier int) returns table (identifier int) as ...
This gives the output row's column a name, which is even more convenient to use.