Re: unique amount more than one table

You can try restricting all name insertions (on any of the tables) to go through one or more functions that serialize amongst themselves. Basically lock a common table and check the view for the new name before inserting.

On Apr 5, 2011, at 18:02, Perry Smith <pedzsan(at)gmail(dot)com> wrote:

> I have five tables each with a "name" field. Due to limitations in my user interface, I want a name to be unique amoung these five tables.
>
> I thought I could first create a view with something like:
>
> SELECT name, 'table1' as type from table1
> UNION ALL
> SELECT name, 'table2' as type from table2
> UNION ALL
> SELECT name, 'table3' as type from table3
> ...
>
> I called this view xxx (I'm just experimenting right now).
>
> I then created a function:
>
> CREATE OR REPLACE FUNCTION unique_xxx ( ) RETURNS boolean AS $$
> SELECT ( SELECT max(cnt) FROM ( SELECT count(*) AS cnt FROM xxx GROUP BY name ) AS foo ) = 1;
> $$ LANGUAGE SQL;
>
> Next I added a check constraint with:
>
> ALTER TABLE table1 ADD CHECK ( unique_xxx() );
>
> A test shows:
>
> select unique_xxx();
> unique_xxx
> ------------
> t
> (1 row)
>
> After I insert a row that I want to be rejected, I can do:
>
> select unique_xxx();
> unique_xxx
> ------------
> f
> (1 row)
>
> but the insert was not rejected. I'm guessing because the check constraint runs before the insert? So, I could change my approach and have my unique_xxx function see if the name to be added is already in the xxx view but it is at that point that I stopped and thought I would ask for advice. Am I close or am I going down the wrong road?
>
> Thank you for your time,
> pedz
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general