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

Geoff Winkless <pgsqlgeneral(at)geoff(dot)dj> writes:
> On 7 February 2013 16:26, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Hmm, interesting hack. I guess that would meet the part of the spec
>> that says, eg, information_schema.constraint_column_usage.column_name
>> must be an identifier --- at least if you also restricted which schema
>> the function could be in.

> Apologies if I'm misunderstanding your point; couldn't you give the index
> name (since we've suggested you would have to have a unique index on the
> function in order to use it as an FK anyway) as the identifier?

My point is that the spec expects that identifier to be the name of a
column in the table, and so will spec-compliant applications. Inventing
different ways to provide an identifier that can be claimed to describe
the functional expression doesn't really do anything to get around that
problem.

I'm inclined to think that the way that the standards committee expects
people to get around this is to store the functional expression
explicitly as a separate column. There's a feature called "generated
columns" in recent versions of the spec that automates that. PG hasn't
implemented generated columns yet, but you can get the same effect with
a BEFORE trigger to calculate the separate column's value.

regards, tom lane