Search in 3 tables using PL/pgSQL functions and triggers-CodePudding

I am new to SQL and still learning functions and triggers. I have 3 tables:

PRODUCTS_BOUGHT

CUSTOMER	DATE	PRODUCTS
3FG	2022-12-15	25
4HZ	2022-12-18	30

PRODUCTS_PRICE:

DATE	TYPE	PRICE
2022-12-15	A	125$
2022-12-18	B	147$

CUSTOMERS_REGISTER:

CUSTOMER	TYPE
3FG	A
4HZ	B

I need to add a column "COST" in the REF table with a value obtained using: COST = PRICE * PRODUCTS. But the function needs to check that the price is applied based on the type of product purchased by the customer in that certain date to obtain something like this:

PRODUCTS_BOUGHT

CUSTOMER	DATE	PRODUCTS	COST
3FG	2022-12-15	25	3125
4HZ	2022-12-18	30	4410

I need to use something like the following:

ALTER TABLE products_bought
ADD COLUMN cost;

CREATE OR REPLACE FUNCTION calc_cost()
RETURNS TRIGGER AS $$
BEGIN

END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE TRIGGER cost_trigger
BEFORE INSERT OR UPDATE ON products_bought
FOR EACH ROW
EXECUTE FUNCTION calc_cost();

I have been trying creating the column first and then adding the value like this:

ALTER TABLE products_bought
ADD COLUMN cost;

CREATE OR REPLACE FUNCTION calc_cost()
RETURNS TRIGGER AS $$
BEGIN
  SELECT(products_bought.products * products_price.price) INTO cost
  FROM products_bought, products_price, customers_register
  WHERE products_bought.rf_date = products_price.fp_date AND
    customers_register.type = customers_register.type;
  RETURN cost;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE TRIGGER cost_trigger
BEFORE INSERT OR UPDATE ON products_bought
FOR EACH ROW
EXECUTE FUNCTION calc_cost();

CodePudding user response：

The returning trigger function should return the NEW instead of only the column affected. notice that the insert into is also beign inserted at the NEW.cost value. You can look here : PostgreSQLTriggers for trigger default values like NEW , OLD from the row it is beign edited.

CREATE OR REPLACE FUNCTION calc_cost()
RETURNS TRIGGER AS $$
BEGIN

SELECT(products_bought.products * products_price.price) INTO NEW.cost
  FROM products_bought, products_price, customers_register
  WHERE products_bought.rf_date = products_price.fp_date AND
    customers_register.type = customers_register.type;
RETURN NEW.*;
END;
$$ LANGUAGE plpgsql;

CodePudding user response：

Selecting from the table products_bought in your trigger function looks like a misunderstanding. The trigger is fired BEFORE INSERT OR UPDATE ON products_bought, so just work with the special NEW record. And make sure you also RETRUN NEW;:

CREATE OR REPLACE FUNCTION calc_cost()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   SELECT INTO NEW.cost
          NEW.products * p.price
   FROM   products_price p
   WHERE  p.fp_date = NEW.rf_date;

   RETURN NEW;
END
$func$;

This only makes sense if there is a single matching row in table products_price. If there can be more, you have to define which row to pick. If there is none, cost will not be assigned.

I also removed the table customers_register from the query, since it didn't seem to do anything useful (unless you wanted to nullify cost if there is no related row in that table, which I doubt.)

PostgreSQL Update trigger