postgresql - pgsql select for update multiple tables -

i have following plpgsql function:

create function user_delete(   in id int4 )   returns void   $body$   begin     delete user_role user_id = id;     delete user_permission user_id = id;     delete permission_cache user_id = id;     delete access user_id = id;     delete "user" user_id = id;   end;   $body$ language plpgsql volatile; 

is possible lock rows related used_id in tables single select update query?

you wouldn't need use explicit locks unless have race condition , heavy concurrent load in application. it's best streamline write access, processes access tables , rows in same order avoid deadlocks. delete commands collects locks fast select update does.

i don't think possible single select update.
lock rows in tables sequentially:

perform 1 user_role x x.user_id = id update; perform 1 user_permission x x.user_id = id update; ... 

locks collected "as go" , released @ end of transaction. since function inside transaction automatically, locks remain @ least until end of function.

• inside plpgsql function use perform instead of select, since don't want rows returned.
• beware of name conflicts. used table-qualified column names in example.