Thread: Transactions and functions

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

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@aklaver.com

On Fri, Sep 23, 2016 at 9:14 PM, Арсен Арутюнян <arutar@bk.ru> wrote:
> would you like to help me with several questions:
> 1)are all functions atomic?

 Yes, of course.

> 2)are they execute in a single query?

Same as executing n-number of SQL statements between BEGIN-COMMIT block.


Regards,
Amul