Re: From select to delete

[Alexander Farber <alexander.farber@xxxxxxxxx>]

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;

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general