Postgres undefined column in trigger

Question

I receive an error when inserting a row in Orderlist for this trigger:

ERROR:  column "qty_in_stock" does not exist
LINE 1: SELECT qty_in_stock > 0
           ^
QUERY:  SELECT qty_in_stock > 0
CONTEXT:  PL/pgSQL function books_upd() line 6 at CASE
****** Error **********

ERROR: column "qty_in_stock" does not exist
SQL state: 42703
Context: PL/pgSQL function books_upd() line 6 at CASE

Here is my code:

INSERT INTO orderlist VALUES (12,1235,6)

CREATE OR REPLACE FUNCTION books_upd()
RETURNS trigger as $bookupd$
BEGIN
UPDATE books
SET qty_in_stock=qty_in_stock - NEW.quantity
WHERE isbn=NEW.isbn;
CASE WHEN qty_in_stock > 0 THEN
UPDATE stockmanager
set quantity=quantity+NEW.quantity
WHERE isbn=NEW.isbn;
END CASE;   

RETURN NEW;
END;
$bookupd$ LANGUAGE plpgsql;

Many forum suggestions for putting quote marks around qty_in_stock - same error.

Table info:

Table books: ""isbn""-pk ""title"" ""author"" ""qty_in_stock"" ""price"" ""cost"" ""year-published"" ""publisherid""

Table stockmanager: ""isbn""-pk,fk ""quantity""

Any helps appreciated.

user330315 · Accepted Answer · 2015-12-02 15:55:45Z

1

You can't use a CASE statement like that and you can't reference a table's column outside of a SQL statement. What you need to do is to store the new value in a variable and then use that with an if statement:

CREATE OR REPLACE FUNCTION books_upd()
  RETURNS trigger as 
$bookupd$
DECLARE 
  l_qty integer;
BEGIN
  UPDATE books
    SET qty_in_stock = qty_in_stock - NEW.quantity
  WHERE isbn=NEW.isbn
  RETURNING qty_in_stock INTO l_qty;

  if l_qty > 0 THEN
    UPDATE stockmanager
       set quantity= quantity + NEW.quantity
    WHERE isbn = NEW.isbn;
  END IF;   

  RETURN NEW;
END;
$bookupd$ 
LANGUAGE plpgsql;

answered Dec 2, 2015 at 15:55

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres undefined column in trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related