In-Reply-To: <468E54D7.8020807@andromeiberica.com>
References: <468E54D7.8020807@andromeiberica.com>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <9583C488-0CE8-46FE-A58F-9D2DC8B6E201@seespotcode.net>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: Advice about how to delete
Date: Fri, 6 Jul 2007 10:04:54 -0500
To: arnaulist@andromeiberica.com


On Jul 6, 2007, at 9:42 , Arnau wrote:

>   I have the following scenario, I have users and groups where a  
> user can belong to n groups, and a group can have n users. A user  
> must belogn at least to a group. So when I delete a group I must  
> check that there isn't any orphan. To do this I have something like  
> that:


>       IF v_count = 1 THEN
>         DELETE FROM users WHERE user_id = result.user_id;
>         v_deleted = v_deleted + 1;
>       END IF;

Am I right in reading that you're deleting any users that would be  
orphans? If so, you can just delete the orphans after rather than  
delete them beforehand (untested):

-- delete user_group 
DELETE FROM user_groups
WHERE user_group_id = p_group_id;

-- delete users that don't belong to any group
DELETE FROM users
WHERE user_id IN (
     SELECT user_id
     LEFT JOIN user_groups
     WHERE group_id IS NULL);

This should execute pretty quickly. You don't need to loop over any  
results. Remember, SQL is a set-based language, so if you can pose  
your question in a set-based way, you can probably find a pretty  
good, efficient solution.

Michael Glaesemann
grzm seespotcode net