Simple update trigger on PostgreSQL returning errors

Question

CREATE FUNCTION update_status() RETURNS TRIGGER AS ' 
BEGIN 
SELECT status FROM animals
IF status = "Alive" 
THEN 
UPDATE animals SET status = "Sold" WHERE status="Alive";   
END IF;   
RETURN NULL; 
END; ' LANGUAGE plpgsql;  

CREATE TRIGGER updatetrigger AFTER UPDATE OF id_selling ON animals EXECUTE PROCEDURE     update_status(); 

It says I'm not using the selected attributes, and I should use Perform instead of Select. But then it doesnt recognize the IF after the perform. I'm really new to these syntax, so it might be wrong. I think its clear what I want to do (and i believe it simple for someone with experience). Can you please help me with this problem?

Answer 1

In standard SQL (and PostgreSQL), this is a quoted identifier (table name, column name, ...):

"Alive"

and this is a string literal:

'Alive'

You will be getting complaints about "unknown columns" because you're using the wrong quotes for your strings.

Functions are usually defined with dollar-quoting to avoid this sort of thing:

CREATE FUNCTION update_status() RETURNS TRIGGER AS $$
BEGIN 
    SELECT status FROM animals
    IF status = 'Alive' THEN 
        UPDATE animals SET status = 'Sold' WHERE status = 'Alive';   
    END IF;   
    RETURN NULL; 
END;
$$ LANGUAGE plpgsql;

Triggers have access to NEW and OLD to represent the previous/current state of the row and the new/updated state of the row, you should be consulting those instead of trying to SELECT to find the status:

CREATE FUNCTION update_status() RETURNS TRIGGER AS $$
BEGIN 
    IF NEW.status = 'Alive' THEN 
        UPDATE animals SET status = 'Sold' WHERE status = 'Alive';   
    END IF;   
    RETURN NULL; 
END;
$$ LANGUAGE plpgsql;

You might want a little more in the WHERE clause for that UPDATE too, just WHERE status = 'Alive' seems a bit broad.