6
Stored procs are the programming API of the database, the methods you invoke to mutate the system state. As such, I prefer to have SPs names reflect the business outcome desired rather than the implementation - change_of_address rather than update_customer. Each SP has the parameters to perform that business change and no others. Each does one thing and does ...
3
Define the constraint as deferrable:
CREATE TABLE widget (
id serial PRIMARY KEY,
name text NOT NULL,
ordinal int NOT NULL UNIQUE DEFERRABLE
);
Then you can update it in one statement:
update widget
set ordinal = t.new_ordinal
from (
values (1, 3), (3,1)
) as t(id, new_ordinal)
where t.id = widget.id
2
Personally I don't understand why people write stored procedures that only perform CRUD. For me SQL is the interface to the database. Just write an update statement with placeholders for the column values and send that to the database -- along with the bound variables.
Someone will argue about SQL injection, but a) you're just moving the point where that can ...
1
A plain UNIQUE constraint is NOT DEFERRABLE by default. Unique violations are checked after each row. The manual phrases this as:
checked immediately after every command
But it's really checked for every individual written row.
If you define the UNIQUE constraint DEFERRABLE (like a_horse provided), unique violations are checked after each statement. ...
1
Just use your "source" as supplier for it.
Because you are using variables , which holds only one value , this is way is working fine with one statement.
MERGE and trigger statements can work with multiple rows at once, not just one , so just use the special INSERTED table inside the MERGE statement; just replace the your MERGE statement with next ...
Only top voted, non community-wiki answers of a minimum length are eligible
