Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id EFC112E0298 for ; Fri, 7 Mar 2008 11:47:04 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 82005-04 for ; Fri, 7 Mar 2008 11:46:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.156]) by postgresql.org (Postfix) with ESMTP id AFF8C2E00BD for ; Fri, 7 Mar 2008 11:46:55 -0400 (AST) Received: by fg-out-1718.google.com with SMTP id 22so420051fge.23 for ; Fri, 07 Mar 2008 07:46:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=T6TAWPwPbNCFD5QQIe/OQQZ6hinYyV+ZEU2e8Ty5tus=; b=fn/eGUKPBtu0CZzTNDAlk1OYAlrGyqHoMDAnZAxm3P02drUp4IR4SLm8bpOzlXQYT8BW6/NLp6oNtux1hqC8D3d8Rv0rpoFipA2xxfLkbUHRysEurmWYIHrY3bXB/N2dejevtouKMxzqLQrMOsiLgj7UpoNybDnxJgclZ1omc6g= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=l7zTFq3wLMkaXaeIwFCRaKI0zGgHIQra9ROc1SCsGmYmzw6svVszoeem86nPLUolCiIQk/u/dgg4eyPl1YtTU1iUY9k8GurGSWZnPZC1QPoHLvFH+kUh3C1K0BJlUE7rgOtD5nclf/AvTY+6TH8kbXj8+PJah5Qg37dLZts2KBc= Received: by 10.86.62.3 with SMTP id k3mr1669459fga.8.1204904813445; Fri, 07 Mar 2008 07:46:53 -0800 (PST) Received: by 10.86.86.18 with HTTP; Fri, 7 Mar 2008 07:46:53 -0800 (PST) Message-ID: Date: Fri, 7 Mar 2008 15:46:53 +0000 From: "Jamie Tufnell" To: pgsql-sql@postgresql.org Subject: Re: Composite UNIQUE across two tables? In-Reply-To: <47D110C0.A3DD.0030.0@indicator.be> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <47D100E0.A3DD.0030.0@indicator.be> <20080307083421.26A9D700009D@mwinf2f21.orange.fr> <47D110C0.A3DD.0030.0@indicator.be> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200803/44 X-Sequence-Number: 30372 Hi Bart, On 3/7/08, Bart Degryse wrote: > I haven't tested these two statements, but I'm using exactly this > concept on some tables myself. > My equivalent of your users table contains some 3,000,000 records. > My equivalent of your sites table contains some 150,000 records. > And it works fine... > > CREATE OR REPLACE FUNCTION "fnc_idx_sitegroupid" (p_siteid > sites.id%TYPE) RETURNS site_groups.id%TYPE AS > $body$ > DECLARE > v_sitegroupid site_groups.id%TYPE ; > BEGIN > SELECT site_group_id INTO v_sitegroupid FROM sites WHERE id = > p_siteid; > RETURN v_sitegroupid; > END; > $body$ > LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY > INVOKER; > > CREATE UNIQUE INDEX "users_unq" ON "users" > USING btree ("username", (fnc_idx_sitegroupid(site_id))); Thank you for your suggestion and example! I really like this idea but I haven't been able to get it to work. When I try to create the index I get the following error: ERROR: functions in index expression must be marked IMMUTABLE After consulting the docs (http://www.postgresql.org/docs/8.2/static/sql-createfunction.html) I get the impression I shouldn't declare this function IMMUTABLE since it queries the database? It seems to me it should be STABLE. Out of curiosity, I declared it IMMUTABLE and it worked for the purposes of my small, isolated test,. Am I opening myself up to problems by doing this? Cheers, J.