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 · Accepted Answer · 2016-10-07 00:51:00Z

1

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;

answered Oct 7, 2016 at 0:51

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

POSTGRESQL Function to update a field in table

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