Question 90485 in PostgreSQL Question posted in
The official documentation can be found here.

Search in 3 tables using PL/pgSQL functions and triggers – Postgresql

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

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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.)

    Related:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search