From: Jamie Tufnell <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Composite UNIQUE across two tables?
Date: Fri, 7 Mar 2008 15:46:53 +0000
Message-ID: <b0a4f3350803070746w17c7c29dhdef57ada7568cdc0@mail.gmail.com> (raw)
In-Reply-To: <47D110C0.A3DD.0030.0@indicator.be>
References: <47D100E0.A3DD.0030.0@indicator.be>
<20080307083421.26A9D700009D@mwinf2f21.orange.fr>
<47D110C0.A3DD.0030.0@indicator.be>
Hi Bart,
On 3/7/08, Bart Degryse <Bart.Degryse@indicator.be> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: diesql@googlemail.com
Subject: Re: Composite UNIQUE across two tables?
In-Reply-To: <b0a4f3350803070746w17c7c29dhdef57ada7568cdc0@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox