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.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
