POSTGRESQL Function to update a field in table

Question

This is my first week using postgres and I am trying to update the column status when there is an update performed on the row but keep getting a syntax error. I spent a couple hours trying different things with no luck. Any help is much appreciated.

Table

create table cyclecounts(
id integer
first_name char(50)
last_name char(50)
status char(20));

Function

CREATE FUNCTION status() 
RETURNS trigger as $update_status$
BEGIN
--check status of status field
if new.status = 'unprocessed' then
new.status = 'sent';
RETURN NEW;
END;
$update_status$ 
LANGUAGE plpgsql;

Error: Syntax error at the or near ";" Line 8 END;

Trigger

CREATE TRIGGER status BEFORE UPDATE ON status
FOR EACH ROW EXECUTE PROCEDURE status();

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-10-07T00:51:00

You are missing the end if (at least):

CREATE FUNCTION status() 
RETURNS trigger as $update_status$
BEGIN
--check status of status field
    if new.status = 'unprocessed' then
        new.status = 'sent';
    end if;
    RETURN NEW;
END;
$update_status$ 
LANGUAGE plpgsql;

POSTGRESQL Function to update a field in table

1 Answers