Обсуждение: From select to delete
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
First half is
delete from pref_scores where gid in
(select gid from pref_scores where id=_id);
but how to clean pref_games?
Nevermind, sorry - I've figured it out myself (for a change)
delete from pref_scores where id<>_id and gid in
(select gid from pref_scores where id=_id);
delete from pref_games where gid in
(select gid from pref_scores where id=_id);
delete from pref_scores where id=_id;
Regards
Alex
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber Sent: Friday, October 28, 2011 12:34 PM To: pgsql-general Subject: [GENERAL] From select to delete Hello, 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? ---/Original Message------ The easiest way is to create FOREIGN KEY relationships between the various tables and allow "ON DELETE CASCADE" to do the work. Otherwise you need to DELETE with an appropriate WHERE clause (and sub-selects) or you can use the "USING" clause with the DELETE to add additional condition tables. DELETE FROM t1 USING (t2 JOIN t3 ON (...)) t_linked WHERE t1.something = t_linked.something; See the SQL Command (DELETE) section of the documentation for more details and examples of DELETE See the SQL Command (CREATE TABLE ) section of the documentation for information on how to setup FOREIGN KEYS David J.
Thank you, David - On Fri, Oct 28, 2011 at 7:00 PM, David Johnston <polobo@yahoo.com> wrote: > The easiest way is to create FOREIGN KEY relationships between the various > tables and allow "ON DELETE CASCADE" to do the work. > > Otherwise you need to DELETE with an appropriate WHERE clause (and > sub-selects) or you can use the "USING" clause with the DELETE to add > additional condition tables. > > DELETE FROM t1 > USING (t2 JOIN t3 ON (...)) t_linked > WHERE t1.something = t_linked.something; > I'll try that. My own 3 commands suggested above fail with Foreign key violation: 7 ERROR: update or delete on table "pref_games" violates foreign key constraint "pref_scores_gid_fkey" on table "pref_scores" DETAIL: Key (gid)=(1998) is still referenced from table "pref_scores". CONTEXT: SQL statement "delete from pref_games where gid in (select gid from pref_scores where id= $1 )" Regards Alex
For now I'm trying to finish my 1st approach
(without "on delete cascade") and the following
strangely fails with
ERROR: syntax error at "temp"
DETAIL: Expected record variable, row variable, or list of scalar
variables following INTO.
CONTEXT: compilation of PL/pgSQL function "pref_delete_user" near line 3
create or replace function pref_delete_user(_id varchar,
_reason varchar) returns void as $BODY$
begin
select gid into temp temp_gids from pref_scores where id=_id;
delete from pref_scores where gid in
(select gid from pref_scores where id=_id);
delete from pref_games where gid in
(select gid from pref_scores where 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;
Eventhough the same command works ok on the psql-prompt:
# select gid into temp temp_gids from pref_scores where id='OK446163742289';
SELECT
# select * from temp_gids ;
gid
------
1895
1946
1998
2094
2177
2215
(6 rows)
On 28 Oct 2011, at 19:40, Alexander Farber wrote: > For now I'm trying to finish my 1st approach > (without "on delete cascade") and the following > strangely fails with > > ERROR: syntax error at "temp" > DETAIL: Expected record variable, row variable, or list of scalar > variables following INTO. > CONTEXT: compilation of PL/pgSQL function "pref_delete_user" near line 3 SELECT INTO in PL/pgSQL isn't the same command as SELECT INTO in SQL. Check the documentation for the two ;) Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling.
On Fri, Oct 28, 2011 at 7:49 PM, Alban Hertroys <haramrae@gmail.com> wrote: > SELECT INTO in PL/pgSQL isn't the same command as SELECT INTO in SQL. > > Check the documentation for the two ;) > > Alban Hertroys Thanks and I'm not surprised about this news...
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;
----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber Sent: Friday, October 28, 2011 1:03 PM Cc: pgsql-general Subject: Re: [GENERAL] From select to delete Thank you, David - On Fri, Oct 28, 2011 at 7:00 PM, David Johnston <polobo@yahoo.com> wrote: > The easiest way is to create FOREIGN KEY relationships between the > various tables and allow "ON DELETE CASCADE" to do the work. > > Otherwise you need to DELETE with an appropriate WHERE clause (and > sub-selects) or you can use the "USING" clause with the DELETE to add > additional condition tables. > > DELETE FROM t1 > USING (t2 JOIN t3 ON (...)) t_linked > WHERE t1.something = t_linked.something; > I'll try that. My own 3 commands suggested above fail with Foreign key violation: 7 ERROR: update or delete on table "pref_games" violates foreign key constraint "pref_scores_gid_fkey" on table "pref_scores" DETAIL: Key (gid)=(1998) is still referenced from table "pref_scores". CONTEXT: SQL statement "delete from pref_games where gid in (select gid from pref_scores where id= $1 )" Regards Alex ------/Original Message -------- Ok. And your question is? Dave
On 28 Oct 2011, at 19:02, Alexander Farber wrote: > I'll try that. > > My own 3 commands suggested above fail with > > Foreign key violation: 7 ERROR: update or delete on table "pref_games" > violates foreign key constraint "pref_scores_gid_fkey" on table > "pref_scores" DETAIL: Key (gid)=(1998) is still referenced from table > "pref_scores". CONTEXT: SQL statement "delete from pref_games where > gid in (select gid from pref_scores where id= $1 )" It would without cascades defined, yeah. Did you skip over the first paragraph of David's reply? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest.
I've got another great advice to use DELETE ... USING
instead of DELETE ... (SUB-SELECT),
so my procedure looks now like this (for archive):
create or replace function pref_delete_user(_id varchar,
_reason varchar) returns void as $BODY$
begin
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 p
using temp_gids t
where p.gid = t.gid;
delete from pref_games p
using temp_gids t
where p.gid = t.gid;
/* more delete's here */
end;
$BODY$ language plpgsql;