I have three tables: prescriptions, items, and orders. The prescriptions and items table both have a non nullable column called price. In the orders table, I have multiple columns.

1. type - enum of either 'prescription' or 'item'
2. amount - non nullable int with default of 1
3. price

I'm trying to create trigger that checks whether the order type is prescription or order, and inserts the price multiplied by the amount into the orders table. I'm also throwing an error if the price is manually inserted, because I only want it to be calculated on the server side.

My code so far is this.

CREATE OR REPLACE FUNCTION trg_orders_check()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
  IF NEW.type = 'prescription' AND NEW.prescription_id IS NULL THEN
    RAISE EXCEPTION 'Must include prescription_id';
  ELSIF NEW.TYPE = 'prescription' THEN
    base_price := SELECT price FROM prescriptions
    WHERE prescription_id = NEW.prescription_id;
    INSERT INTO NEW (price) VALUES (base_price * NEW.amount);
  ELSIF NEW.TYPE = 'item' AND NEW.item_id IS NULL THEN
    RAISE EXCEPTION 'Must include item_id';
  ELSIF NEW.TYPE = 'prescription' THEN
    base_price float := SELECT price FROM orders
    WHERE item_id = NEW.item_id;
    INSERT INTO NEW (price) VALUES (base_price * NEW.amount);
  ELSE
    RAISE EXCEPTION 'Type must be either prescription or item';
  END IF;
END
$func$;

Right now I'm getting the error "base_price" is not a known variable

I get the same error if I do base_price float := ...expression

I'm wondering what is causing that error, and more broadly if this is the correct approach for creating the trigger I outlined above. Any help would be appreciated.

CodePudding user response：

This syntax for variable declaration is not supported. You should to use DECLARE statement before block.

DECLARE x int;
BEGIN
  x := 10;
END