X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 20B07D1B4E6
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 26 Sep 2003 23:45:12 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 43572-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 26 Sep 2003 20:44:44 -0300 (ADT)
Received: from bast.unixathome.org (bast.unixathome.org [66.11.174.150])
	by svr1.postgresql.org (Postfix) with ESMTP id 0C6B2D1B4F0
	for <pgsql-sql@postgresql.org>; Fri, 26 Sep 2003 20:44:41 -0300 (ADT)
Received: from laptop.unixathome.org (laptop.unixathome.org [192.168.0.34])
	by bast.unixathome.org (Postfix) with ESMTP id 618A83F53
	for <pgsql-sql@postgresql.org>; Fri, 26 Sep 2003 19:44:20 -0400 (EDT)
Received: from localhost (localhost [127.0.0.1])
	by laptop.unixathome.org (Postfix) with ESMTP id 5BC15322
	for <pgsql-sql@postgresql.org>; Fri, 26 Sep 2003 19:47:06 -0400 (EDT)
Date: Fri, 26 Sep 2003 19:47:06 -0400 (EDT)
From: Dan Langille <dan@langille.org>
X-X-Sender: dan@laptop.unixathome.org
To: pgsql-sql@postgresql.org
Subject: Removing simliar elements from a set
Message-ID: <20030926184440.B301@laptop.unixathome.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/345
X-Sequence-Number: 15323

Hi folks,

I'm trying to remove items from a set which are similar to items in
another set.

This is the set (MASTER) from which I wish to remove items:

 /ports/Mk/bsd.python.mk
 /ports/lang/python-doc-html/distinfo
 /ports/lang/python/Makefile
 /ports/lang/python/distinfo
 /ports/lang/python/files/patch-Modules-Setup.dist

These are the items (MATCHES) which are the prefixes which must be
removed:

 /ports/lang/python-doc-html
 /ports/lang/python

In this case, the answer would be:

 /ports/Mk/bsd.python.mk

In short, we remove all items from MASTER which are under the directories
specified in MATCHES.

My first attempt, which works only if MATCHES contains one item:

SELECT *
  FROM MASTER JOIN MATCHES
    ON NOT (MASTER.pathname ~ ('^' || MATCHES.pathname || '/.+'));

However, if there is more than one row in MATCHES, this will not work.

Clues please?
-- 
Dan Langille - http://www.langille.org/