From: | Арсен Арутюнян <arutar(at)bk(dot)ru> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Transactions and functions |
Date: | 2016-09-23 15:44:02 |
Message-ID: | 1474645442.389960547@f336.i.mail.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
would you like to help me with several questions:
1)are all functions atomic?
2)are they execute in a single query?
--
Arsen Arutyunyan
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-09-23 16:44:02 | Re: Transactions and functions |
Previous Message | Deven Phillips | 2016-09-23 14:19:12 | Re: jsonb_set for nested new item? |