Search in 3 tables using PL/pgSQL functions and triggers

Question

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

SET or SELECT? And where does the variable "cost" come from? A trigger function should also return NEW, OLD or NULL, depending on your goal. I would also use the JOIN syntax when writing a join, just to make it easier to understand and to create less bugs to debug. — Frank Heikens, Dec 18 '22 at 19:40
Its SELECT sorry I have edited it. I was thinking of inserting that value into the column created above. — Moses Casposes, Dec 18 '22 at 20:53
Then you need to return NEW.cost and not just "cost". Using a JOIN would be nice, this is standard since 1992, 30 years ago... And it avoids bugs because your code becomes better readable. — Frank Heikens, Dec 18 '22 at 21:02

score 0 · Answer 1 · answered Dec 19 '22 at 00:48

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;

Thank you so much, I was able to resolve the problem using both answers. I didn´t understand how to use NEW so using your function but changing the second condition with a GROUPBY I got the right result. — Moses Casposes, Dec 19 '22 at 20:10

score 0 · Accepted Answer · answered Dec 19 '22 at 03:31

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

As you pointed out, I didn´t understand how to use NEW. Using both answers I was able to resolve the problem. Thank you so much. — Moses Casposes, Dec 19 '22 at 20:11

Search in 3 tables using PL/pgSQL functions and triggers

2 Answers2