Missing FROM clause entry for table "product"

Question

CREATE OR REPLACE FUNCTION update_sellstatus()
  RETURNS trigger AS
$body$
BEGIN
    if products."sellStatus" = "Sold" then
            update auctions
            set "auctionStatus" = "End"
            where (products."id" = auctions."idProduct");
        end if;
END
$body$
language plpgsql

create trigger update_sellStatus_auctionStatus
after update on products
for each row
    execute procedure update_sellstatus()

ERROR: missing FROM-clause entry for table "product"

I want to UPDATE column auctions.auctionStatus = "End" after UPDATE of column products.sellStatus = "Sold" where (products."id" = auctions."idProduct").

So I create a trigger but I got an error. It's make me can't UPDATE table products

Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… — a_horse_with_no_name

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2020-02-26T10:43:56

You can't reference a table like in a function. But as this is a trigger. As documented in the manual you can access the values of the update row using the new record:

Also: string constants need to be enclosed in single quotes in SQL, double quotes are only needed for identifiers (and should be avoided like the plague).
"Sold" refers to a column, 'Sold' is a character (string) value

CREATE OR REPLACE FUNCTION update_sellstatus()
  RETURNS trigger AS
$body$
BEGIN
  if new."sellStatus" = 'Sold' then
    update auctions
      set "auctionStatus" = 'End'
    where auctions."idProduct" = new."id";
  end if;
END
$body$
language plpgsql

Missing FROM clause entry for table "product"

1 Answers