From select to delete

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Hello,

in PostgreSQL 8.4.9 I'm able to
select all games and his partners by a player id:

# select * from pref_scores s1
join pref_scores s2 using(gid)
join pref_games g using(gid)
where s1.id='OK531282114947';

 gid  |       id       | money | quit |         id          | money |
quit | rounds |          finished
------+----------------+-------+------+---------------------+-------+------+--------+----------------------------
  321 | OK531282114947 |   218 | f    | OK531282114947      |   218 |
f    |     17 | 2011-10-26 17:16:04.074402
  321 | OK531282114947 |   218 | f    | OK501857527071      |  -156 |
f    |     17 | 2011-10-26 17:16:04.074402
  321 | OK531282114947 |   218 | f    | OK429671947957      |   -62 |
f    |     17 | 2011-10-26 17:16:04.074402
 1665 | OK531282114947 |    35 | f    | OK356310219480      |   433 |
f    |     37 | 2011-10-27 09:37:15.702893
 1665 | OK531282114947 |    35 | f    | VK670840            |  -469 |
f    |     37 | 2011-10-27 09:37:15.702893
 1665 | OK531282114947 |    35 | f    | OK531282114947      |    35 |
f    |     37 | 2011-10-27 09:37:15.702893

But simple replacing of "select *" by "delete"
doesn't work here anymore.

Is there maybe an easy way to delete that player
(and all his games and partners) or
do I have to work with temp tables?

This is a table holding all games:

# select * from pref_games where gid=321;
 gid | rounds |          finished
-----+--------+----------------------------
 321 |     17 | 2011-10-26 17:16:04.074402
(1 row)

This are scores reached by 3 players in that game:

# select * from pref_scores where gid=321;
       id       | gid | money | quit
----------------+-----+-------+------
 OK531282114947 | 321 |   218 | f
 OK501857527071 | 321 |  -156 | f
 OK429671947957 | 321 |   -62 | f

I'd need to clean all tables when deleting a user:

        create or replace function pref_delete_user(_id varchar)
            returns void as $BODY$
                begin

               /* XXX this won't work of course */

                delete from pref_scores s1
                join pref_scores s2 using(gid)
                join pref_games g using(gid)
                where s1.id=_id;

                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;

Thank you for any hints
Alex

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