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 <pedz...@gmail.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
