PostgreSQL Calculated Column with values of another table referenced by foreign key

Question

I'm currently working on a simple dummy project to refresh my knowledge on SQL and to learn a few new things :)

I have a table Article with the columns:

aID, price 

I have another table Storage:

sID, aID, count  

The Storage table references the aID as a foreign key and the count column say how much of an article is stored.

Now I want to add a column value to my Storage table. This column should be calculated by Article.price * Storage.count.

I found after searching the web that you can have calculated columns like this

CREATE TABLE tbl 
(
     int1 INT,
     int2 INT,
     product BIGINT GENERATED ALWAYS AS (int1 * int2) STORED
);

But I haven't found an example how to this with columns from another table.

What do I have to do in order to use the price from the referenced aID in the calculation?

Answer 1

You cannot define a generated column based on values from other tables. Per the documentation:

The generation expression can refer to other columns in the table, but not other generated columns. Any functions and operators used must be immutable. References to other tables are not allowed.

You can achieve the expected behavior by creating two triggers on both tables but usually creating a view based on the tables is a simpler and more efficient solution.