This seems to work (I'm not sure if ON COMMIT DROP
is needed or not - I'm using non-persistent PHP 5.3 script
with pgbouncer pool_mode=session and PostgreSQL 8.4.9):
create or replace function pref_delete_user(_id varchar,
_reason varchar) returns void as $BODY$
begin
insert into pref_ban select
id,
first_name,
last_name,
city,
last_ip
from pref_users where id=_id;
update pref_ban set reason=_reason where id=_id;
create temporary table temp_gids (gid int not null) on
commit drop;
insert into temp_gids (gid) select gid from
pref_scores where id=_id;
delete from pref_scores where gid in (select gid from
temp_gids);
delete from pref_games where gid in (select gid from temp_gids);
delete from pref_rep where author=_id;
delete from pref_rep where id=_id;
delete from pref_catch where id=_id;
delete from pref_game where id=_id;
delete from pref_hand where id=_id;
delete from pref_luck where id=_id;
delete from pref_match where id=_id;
delete from pref_misere where id=_id;
delete from pref_money where id=_id;
delete from pref_pass where id=_id;
delete from pref_status where id=_id;
delete from pref_users where id=_id;
end;
$BODY$ language plpgsql;
