Re: Foreign Key normalization question

On Tue 02 Sep 2008 05:35:25 PM EDT, D. Dante Lorenso wrote:
> You could add a trigger to your product_location table that just
> double-checked the customers matched or prevents the insert/update.  A
> PL/PGSQL function like this might help:
>
> ---------- 8< -------------------- 8< ----------
>
> DECLARE
>    is_ok BOOLEAN;
> BEGIN
>    SELECT p.customer_id = l.customer_id
>    INTO is_ok
>    FROM product p, location l
>    WHERE p.product_id = NEW.product_id
>    AND l.location_id = NEW.location_id;
>
>    -- didnt find the product and location ... weird
>    IF NOT FOUND THEN
>        RETURN NULL;
>    END;
>
>    -- product customer matches the location customer
>    IF is_ok = TRUE THEN
>        RETURN NEW;
>    END;
>
>    -- product and location customers did NOT match, reject changes
>    RETURN NULL;
> END;
> ---------- 8< -------------------- 8< ----------
>
> Disclaimer: I have no idea if that code works.  I just whipped it up now
> without testing it.  That might do your checks without having to add
> columns to tables you don't want to add.

Thanks!  This is what I was looking for.  Although I got a few syntax
errors in postgreSQL 8.3 until I changed a few END; statements to END
IF;

Also, I had to put:

    create or replace function check_customer ()
    returns trigger $$

at the top of this, and

$$ language 'plpgsql';

at the bottom.  I'm a novice at writing triggers, and this is really
useful.

Thanks again.

Matt