Is it possible to refer a column in a view as foreign key (PostgreSQL 9.4)?

Question

I know in older versions it was impossible, is it the same with version 9.4?

I'm trying to do something like this:

CREATE VIEW products AS 
    SELECT d1.id AS id, d1.price AS pr FROM dup.freshProducts AS d1
    UNION
    SELECT d2.id AS id, d2.price AS pr FROM dup.cannedProducts AS d2;

CREATE TABLE orderLines
( 
    line_id integer PRIMARY KEY, 
    product_no integer REFERENCES productView.id
);

I'm trying to implement an inheritance relationship where freshProducts and cannedProducts both inherit from products. I implemented it using two different tables and I created a view products that has only the common properties between freshProducts and cannedProducts. In addition, each row in orderLines has a relationship with a product, either a freshProduct or a cannedProduct. See image for clarification.

If referencing to a view is yet not possible, which solution do you think is best? I've thought of eihter a materialized view or implementing the restriction using triggers. Could you recommend any good example of such triggers to use as a basis?

Thank-you very much!

Answer 1

Referencing a (materialized) view wouldn't work and a trigger might look like this:

CREATE OR REPLACE FUNCTION reject_not_existing_id()
    RETURNS "trigger" AS
    $BODY$
        BEGIN
            IF NEW.product_no NOT IN (SELECT id FROM dup.freshProducts UNION SELECT id FROM dup.cannedProducts) THEN
                RAISE EXCEPTION 'The product id % does not exist', NEW.product_no;
            END IF;
            RETURN NEW;
        END;
    $BODY$
        LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER tr_before_insert_or_update
    BEFORE INSERT OR UPDATE OF product_no
    ON orderLines
    FOR EACH ROW
    EXECUTE PROCEDURE reject_not_existing_id();

(See also http://www.tek-tips.com/viewthread.cfm?qid=1116256)

A materialized view might look like a good approach but fails for two reasons: Like a view you simply can't reference it, because it is no table (go ahead and try). Assuming you could, there would still be the problem of preventing two equal ids in freshProducts and cannedProducts. Yes you can define an UNIQUE INDEX on a materialized view, but how to make sure the same id isn't used both in fresh an canned in the first place? That's something you still have to solve if using the trigger in orderLines.

That brings me to suggest to rethink your model. 'Fresh' and 'canned' might as well be values of an attribute of a single table products, hence making all the trouble superfluous. If fresh and canned product significantly differ in (the number of) their attributes (can't think of any other reason to create two different tables) then reference the product id in two other tables. Like

CREATE TABLE products
(
    id ... PRIMARY KEY
    , fresh_or_canned ...
    , price ...
    , another_common_attribute_1 ...
    , ...
    , another_common_attribute_n ...
);

CREATE TABLE canned_specific_data
(
    canned_id ... REFERENCES products (id)
    , type_of_can ...
    , ...
    , another_attribute_that_does_not_apply_to_fresh ...
);

CREATE TABLE fresh_specific_data
(
    fresh_id ... REFERENCES products (id)
    , date_of_harvest ...
    , ...
    , another_attribute_that_does_not_apply_to_canned ...
);