CODEWITHSUNDEEP
postgresql
Mike Buzaki
Mike Buzaki

Reputation: 35

POSTGRESQL Function to update a field in table

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();

Upvotes: 1

Views: 461

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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;

Upvotes: 1

Related Questions