From: | Perry Smith <pedzsan(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | unique amount more than one table |
Date: | 2011-04-05 22:02:34 |
Message-ID: | 679DDAB8-01B2-4F7F-A51C-A8512C265B07@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2011-04-05 22:28:45 | Re: unique amount more than one table |
Previous Message | John R Pierce | 2011-04-05 19:27:27 | Re: Out of memory |