PostgreSQL：如何创建含自动计算列的card表并实现自动填充？

Absolutely, you can automate this—two practical approaches are database triggers (for physical table population) or views (for real-time computed results). Let's break this down, starting with a critical detail you'll need to resolve first.

First: Clarify the Missing Table Relationships #

Your current schema doesn't explicitly link product_sale and product_price, which is essential for calculating fee and comm. For example:

• How does a seller/country from product_sale connect to a product from product_price?
• Does card.c_date map to product_sale.s_date or product_price.p_date?

For this example, I'll assume:

1. card.c_date = product_sale.s_date
2. There's an intermediate table (e.g., seller_product_map) that links (seller, country) to product (adjust this to match your actual business rules)
3. We'll cast product_price.price to FLOAT (note: storing prices as VARCHAR is not ideal—you should switch this column to a numeric type like DECIMAL(10,2) long-term to avoid conversion errors)

Option 1: Use Triggers for Automatic Physical Table Population #

Triggers run automatically when data is inserted (or updated/deleted) in a table. We'll create two triggers: one for product_sale inserts, one for product_price inserts.

Trigger for product_sale Inserts #

This trigger adds or updates a corresponding row in card whenever a new sale is recorded:

DELIMITER //
CREATE TRIGGER after_product_sale_insert
AFTER INSERT ON product_sale
FOR EACH ROW
BEGIN
    -- Insert new card record, or update if the primary key already exists
    INSERT INTO card (c_date, seller, country, fee, comm)
    SELECT 
        NEW.s_date AS c_date,
        NEW.seller,
        NEW.country,
        CAST(pp.price AS FLOAT) + NEW.b_rate AS fee,
        CAST(pp.price AS FLOAT) - NEW.s_rate AS comm
    FROM product_price pp
    -- Replace this join with your actual logic to link sales to products
    JOIN seller_product_map spm 
        ON spm.product = pp.product 
        AND spm.seller = NEW.seller 
        AND spm.country = NEW.country
    WHERE pp.p_date = NEW.s_date
    ON DUPLICATE KEY UPDATE
        fee = VALUES(fee),
        comm = VALUES(comm);
END //
DELIMITER ;

Trigger for product_price Inserts #

This trigger updates existing card records whenever a new price is added (assuming existing sales should use the latest price):

DELIMITER //
CREATE TRIGGER after_product_price_insert
AFTER INSERT ON product_price
FOR EACH ROW
BEGIN
    UPDATE card c
    JOIN product_sale ps 
        ON c.c_date = ps.s_date 
        AND c.seller = ps.seller 
        AND c.country = ps.country
    JOIN seller_product_map spm 
        ON spm.seller = ps.seller 
        AND spm.country = ps.country 
        AND spm.product = NEW.product
    SET 
        c.fee = CAST(NEW.price AS FLOAT) + ps.b_rate,
        c.comm = CAST(NEW.price AS FLOAT) - ps.s_rate
    WHERE ps.s_date = NEW.p_date;
END //
DELIMITER ;

Option 2: Use a View for Real-Time Computed Results #

If you don't need to physically store the card data (just need to query calculated values on demand), a view is simpler—it automatically reflects the latest data from product_sale and product_price without triggers:

CREATE VIEW card_view AS
SELECT
    ps.s_date AS c_date,
    ps.seller,
    ps.country,
    CAST(pp.price AS FLOAT) + ps.b_rate AS fee,
    CAST(pp.price AS FLOAT) - ps.s_rate AS comm
FROM product_sale ps
JOIN seller_product_map spm 
    ON ps.seller = spm.seller 
    AND ps.country = spm.country
JOIN product_price pp 
    ON spm.product = pp.product 
    AND ps.s_date = pp.p_date;

Query the view just like a regular table:

SELECT * FROM card_view;

Key Notes #

• Fix the price column type: Storing prices as VARCHAR is error-prone. Convert product_price.price to FLOAT or DECIMAL(10,2) to avoid casting issues.
• Adjust join logic: Replace the seller_product_map references with your actual way of linking sales to products (e.g., a direct column in product_sale, or a different intermediate table).
• Handle edge cases: Use LEFT JOIN and COALESCE if you need to gracefully handle cases where a sale has no matching price (or vice versa).

内容的提问来源于stack exchange，提问作者as - if