CODEWITHSUNDEEP
sqlpostgresqlpostgresql-12
KilledByCHeese
KilledByCHeese

Reputation: 872

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

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?

Upvotes: 22

Views: 17228

Answers (2)

Roberto King
Roberto King

Reputation: 11

You can define generated columns referencing other table columns in PostgreSQL through use of a function, given it is immutable and only requires input from the local table.

Lets take an example of an e-commerce site with products sold by users.

CREATE TABLE user (
    user_id UUID
    city VARCHAR
    country VARCHAR
)
CREATE TABLE user_product (
    product_id UUID
    user_id UUID
)

At the moment products are only available in the country the user is from and we would like to place a field on the product table to stash the available location. We can create a function that takes in the product_id from the product table and perform joins onto the user table:

CREATE FUNCTION fetch_product_location(product_id UUID)
RETURNS VARCHAR AS $$
DECLARE available_country VARCHAR ;
BEGIN
    SELECT user.country INTO available_country
    FROM
        user_product
        JOIN user ON user_product.user_id = user.user_id
    WHERE
        user_product.product_id = fetch_product_location.product_id
    ;
    RETURN available_country ;
END
$$
IMMUTABLE
LANGUAGE plpgsql
RETURNS NULL ON NULL INPUT ;

This function can then be used when creating or altering the product table in the generated field:

CREATE TABLE product (
    product_id UUID
    price FLOAT
    available_location VARCHAR GENERATED ALWAYS AS fetch_product_location(product_id)
)

Upvotes: 1

klin
klin

Reputation: 121764

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.

Upvotes: 29

Related Questions