Re: cross-table constraints?

At 3:05pm -0400 Tue, 27 Aug 2013, Michael Swierczek wrote:
>> What would be a good way to reorganize my schema to enable a
>> constraint that effectively limits the allowable rows of cost,
>> subject to the value [from another table and column]?

> I am not a database expert, I don't see a way to enforce what you
> want directly in the uniqueness and foreign key constraints of the
> schema. But I believe you should be able to enforce the constraint
> with a trigger. http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
>
> I may have misunderstood your example, but I think this _might_ do
> what you want:
>
> CREATE FUNCTION [...]

> CREATE TRIGGER cost_trigger BEFORE INSERT OR UPDATE ON cost
> FOR EACH ROW EXECUTE PROCEDURE cost_period_check();

Thank you very much for this example code. That is a couple hours of
tinkering/learning now reduced to changing a couple of characters.

> Does that help?

Indeed it does. I was hoping to avoid a trigger, and that I was simply
missing some requisite understanding of RDB internals and the SQL
language. Alas.

Thanks!

Kevin