Re: feature requests (possibly interested in working on this): functional foreign keys

(Forgot to CC the list)


On Thu, Feb 7, 2013 at 7:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Chris Travers <chris.travers@gmail.com> writes:
> > What would be nice to be able to do is to be able to do something like:
> > ALTER TABLE inet_assignment ADD FOREIGN KEY (network(inet_address))
> > REFERENCES cidr_block(block_def);
>
> > 2.  Are there any other major showstoppers I haven't thought of?
>
> The information_schema can't represent such a thing, and this is
> unfixable without breaking the SQL standard.  I suppose we could omit
> functional FK constraints from the information_schema views, but that's
> not terribly palatable.
>

If this were to be limited to table methods (i.e. functions where
relation.function notation works), would that be sufficiently workable?

>
> Have you considered just storing the network(inet_address) value in a
> separate column (maintained by a BEFORE INSERT/UPDATE trigger) and then
> using a regular FK with that?
>

I have.  Honestly I think the UDF + check + trigger is cleaner.

Best Wishes,
Chris Travers

>
>                         regards, tom lane
>