Re: Composite UNIQUE across two tables?

Hi Bart,

On 3/7/08, Bart Degryse <Bart(dot)Degryse(at)indicator(dot)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.