On 09/23/2016 08:44 AM, Арсен Арутюнян wrote:
Hello all i have the table create table testpr(id serial,priority integer,unique(priority) DEFERRABLE, primary key(id)); and procedure: CREATE OR REPLACE FUNCTION JobPriorityChange(JobId bigint,NewPrior integer) RETURNS void AS $$ DECLARE PrevPrior integer; BEGIN PrevPrior := (select priority from testpr where id=JobId); if PrevPrior > NewPrior then WITH u as(update testpr set priority=NewPrior where id=JobId) update testpr set priority=priority+1 where priority>=NewPrior and priority<PrevPrior and id<>JobId; ELSIF PrevPrior < NewPrior then WITH u as(update testpr set priority=NewPrior where id=JobId) update testpr set priority=priority-1 where priority<=NewPrior and priority>PrevPrior and id<>JobId; END IF; END; $$ LANGUAGE plpgsql; according to: https://www.postgresql.org/docs/9.5/static/plpgsql-structure.html
"Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in. However, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction. For more about that see Section 40.6.6."
would you like to help me with several questions: 1)are all functions atomic? 2)are they execute in a single query?
So yes.
-- Arsen Arutyunyan
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
