postgresql - How to lock(exclusive) a record during a function call in pgsql? -

postgresql - How to lock(exclusive) a record during a function call in pgsql? -

in pgsql, how may lock record during function run? considering next function.

create or replace function foo.bar_func(int)returns int $$ s (select * foo.bar id=$1);-- <--lock fetched row [begin] /* query, update, insert, ... */ select coalesce(s.id,-1) s;--return something. -- <-- release locked row [end] $$ language sql;

i lock row(if found) @ begin of function, till finishes work. how pg_advisory_lock(bigint) work? help here?what difference select update?

select … update expect, namely locks returned rows exclusively until end of current transaction (see here).

advisory locks, on other hand, application defined. held either until end of current transaction or until end of current session (see here). thus, need checked manually , may need release them manually.

if want utilize variables (like s in sample code), have utilize pl/pgsql. however, there doesn't seem way create function transactional. instead, executed in context of surrounding transaction. adding exception clause function causes function wrapped in subtransaction (see here), locks acquired function held until end of surrounding transaction. tested pg 9.3.

postgresql