On 12/04/2013 12:00 PM, Robert Haas wrote:
On Wed, Dec 4, 2013 at 11:44 AM, Andrew Dunstan <and...@dunslane.net> wrote:
Oh. I misinterpreted what this feature was about, then. I thought it
was about restricting the reference to a subset of the *referenced*
table, but it seems to be about restricting the constraint to a subset
of the *referencing* table. I guess they're both useful, but the
syntax...
REFERENCES tab(col) WHERE (stuff)
...sure looks like the WHERE clause is syntactically associated with
the table being referenced. What would we do if we eventually wanted
to support both variants?
Well I guess we could say something like:
FOREIGN KEY (a-col) WHERE (a-condition) REFERENCES b(b-col) WHERE
(b-condition)
But it's somewhat ugly.
The case of restricting the allowed referent rows does look slightly like a
solution in search of a problem, but maybe that's just because I haven't
thought of a use for it yet.
Interestingly, the variant for which you can't think of a use case is
the one I've missed most. Typical examples in my experience are
things like project.project_manager_id references person (id) where
person.is_project_manager, or alert (device_id) references device (id)
where not device.deleted.
OK, those make sense. I wonder whether this should be done via a USING
clause on the constraint that pointed to the partial unique index. Or
would that be too obscure?
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
