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

Question

I know that in older versions this was not possible, is it the same with version 9.4?

I am 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 freshProductsand cannedProductsare both inherited from products. I implemented it using two different tables, and I created a view productsthat has only common properties between freshProductsand cannedProducts. In addition, each line in orderLinesis related to a product, either a freshProduct, or a cannedProduct. See Image for details.

, ? . ?

!

Answer 1

() , :

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();

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

, : , , ( ). , , - freshProducts cannedProducts. , UNIQUE INDEX , , ? , , orderLines.

. "" "" products, . () ( - ), .

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 ...
);

Answer 2

, freshProducts, cannedProducts.

: ? , , , . Alsowise, , , .

, , , .