Postgres Fails to create trigger function; 'syntax error near RETURN'

Question

I am a postgres noob and would like to create a trigger function for my admins table. I am following an example that is online, have checked for similar questions and tried various online examples but i am still stuck.

I have a table admins

CREATE TABLE admins (
admin_name      varchar(15) PRIMARY KEY NOT NULL,
password        text NOT NULL,
telephone       varchar(15) NOT NULL UNIQUE,
email           varchar(30) NOT NULL UNIQUE,
added           timestamp DEFAULT CURRENT_TIMESTAMP,
name            varchar(30) NOT NULL,
active          boolean DEFAULT true     
);

And an audit table aud_admins

CREATE TABLE aud_admins (
admin_name      varchar(15) PRIMARY KEY NOT NULL,
password        text NOT NULL,
telephone       varchar(15) NOT NULL UNIQUE,
email           varchar(30) NOT NULL UNIQUE,
added           timestamp DEFAULT CURRENT_TIMESTAMP,
name            varchar(30) NOT NULL,
active          boolean DEFAULT true,
updated         timestamp DEFAULT CURRENT_TIMESTAMP,  
func            varchar(15)  NOT NULL     
);

I'm trying to create a stored trigger function to update the aud_admins table whenever there is a change but have failed woefully

This is my trigger function code:

CREATE OR REPLACE FUNCTION public.audit_admins()
RETURNS trigger AS
$BODY$
BEGIN 
IF (TG_OP = 'DELETE') THEN
    INSERT INTO aud_admins (admin_name,password,telephone,email,added,name,active,func) VALUES (OLD.admin_name,OLD.password,OLD.telephone,OLD.email,OLD.added,OLD.name,OLD.active,PG_OP)
    RETURN OLD;
ENDIF;
IF (TG_OP = 'INSERT') THEN
     INSERT INTO aud_admins (admin_name,password,telephone,email,added,name,active,func) VALUES (NEW.admin_name,NEW.password,NEW.telephone,NEW.email,NEW.added,NEW.name,NEW.active,PG_OP)
     RETURN NEW;
ENDIF;
IF (TG_OP = 'UPDATE') THEN
    INSERT INTO aud_admins (admin_name,password,telephone,email,added,name,active,func) VALUES (OLD.admin_name,OLD.password,OLD.telephone,OLD.email,OLD.added,OLD.name,OLD.active,PG_OP)
    RETURN NEW;
ENDIF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Every time I run it I get this error

**ERROR:  syntax error at or near "RETURN"
  LINE 7:         RETURN OLD;**

Basic runtime specs:

Postgres 9.5 on Ubuntu 16.04 with pgAdmin3

Thank you for your time.

Your insert statements are missing the trailing semi-colon

guido
– guido

2016-05-29 11:46:22 +00:00
Commented May 29, 2016 at 11:46 — guido
– guido, Commented May 29, 2016 at 11:46

gmiley · Accepted Answer · 2016-05-29 12:03:38Z

1

Your INSERT statements are not terminated with semi-colons. Try updating to the following:

IF (TG_OP = 'DELETE') THEN
    INSERT INTO aud_admins (admin_name,password,telephone,email,added,name,active,func) 
    SELECT OLD.admin_name,OLD.password,OLD.telephone,OLD.email,OLD.added,OLD.name,OLD.active,PG_OP;
    RETURN OLD;
END IF;
IF (TG_OP = 'INSERT') THEN
     INSERT INTO aud_admins (admin_name,password,telephone,email,added,name,active,func) 
     SELECT NEW.admin_name,NEW.password,NEW.telephone,NEW.email,NEW.added,NEW.name,NEW.active,PG_OP;
     RETURN NEW;
END IF;
IF (TG_OP = 'UPDATE') THEN
    INSERT INTO aud_admins (admin_name,password,telephone,email,added,name,active,func) 
    SELECT OLD.admin_name,OLD.password,OLD.telephone,OLD.email,OLD.added,OLD.name,OLD.active,PG_OP;
    RETURN NEW;
END IF;

Updated the inserts, I think you need to actually INSERT using a SELECT. Give the modified code above a try.

edited May 29, 2016 at 12:03

answered May 29, 2016 at 11:14

gmiley

6,6041 gold badge15 silver badges25 bronze badges

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

5 Comments

Ian Mbae Over a year ago

Thanks for your quick response @gmiley , however when I do that I get the error ERROR: syntax error at or near "ENDIF" LINE 9: ENDIF;

gmiley Over a year ago

Made a change to use SELECT with the INSERT. Give that a try. That would be the only other thing I could think of that might make a difference judging by the error you are getting.

Abelisto Over a year ago

@ianmin2 END IF;, with space.

gmiley Over a year ago

Good catch, don't know how I missed that too. Funny thing is, at one point I was suggesting switching to CASE statement, which would have fixed the problem as well.

Ian Mbae Over a year ago

@Abelisto, Thanks for your comment, but I realise that with my version of postgres it works even with spaces.

Collectives™ on Stack Overflow

Postgres Fails to create trigger function; 'syntax error near RETURN'

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related