Thread: PLPGSQL errors

Hi,

I'm getting an error on a function I want to execute and I can't see why.
I've tried a few different things and tweaked my sql without joy so far.
I want to log all inserts or updates on one table to another table.

Below I have my error, function , table schema and sql insert statement.
Thanks
It's a little long and I know anyone on this list has enough work of their
own but what goes around comes around :-)

error:
[postgres@central postgres]$ /usr/local/pgsql/bin/psql demo -f sysinsert.sql
psql:sysinsert.sql:16: NOTICE:  Error occurred while executing PL/pgSQL
function fn_sysmessages_log
psql:sysinsert.sql:16: NOTICE:  at END of toplevel PL block
psql:sysinsert.sql:16: ERROR:  control reaches end of trigger procedure
without RETURN

function :
CREATE function fn_sysmessages_log() RETURNS OPAQUE AS '
BEGIN
INSERT INTO sysmessages_log      (      id,      user_id,      message_date,      message_priority,      message,
status     )
 
VALUES       (       NEW.id,       NEW.user_id,       NEW.message_date,       NEW.message_priority,       NEW.message,
    NEW.status       );
 
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER tr_sysmessages_log AFTER INSERT OR UPDATE ON sysmessages FOR
EACH ROW EXECUTE PROCEDURE fn_sysmessages_log();

and my 2 tables :

create table sysmessages
(   id serial PRIMARY KEY,   user_id integer NOT NULL,   message_date date DEFAULT now() NOT NULL,   message_priority
char(1)CHECK( message_priority IN (1,2,3,4,5,6,7,8,9) )
 
NOT NULL,   message text NOT NULL,   status char(1) CHECK( status IN ('A','P','N') ) NOT NULL
);

create table sysmessages_log
(   log_id serial PRIMARY KEY,   id integer,   user_id integer NOT NULL,   message_date date DEFAULT now() NOT NULL,
message_prioritychar(1) CHECK( message_priority IN (1,2,3,4,5,6,7,8,9) )
 
NOT NULL,   message text NOT NULL,   status char(1) CHECK( status IN ('A','P','N') ) NOT NULL
);

and the insert statement which is causing the error:

INSERT INTO sysmessages
(
user_id,
message_date,
message_priority,
message,
status
)
VALUES
(
101,
'2002-10-10',
1,
'hi',
'A'
)

On Wednesday 09 Oct 2002 11:06 am, Rudi Starcevic wrote:
>
> error:
> [postgres@central postgres]$ /usr/local/pgsql/bin/psql demo -f
> sysinsert.sql psql:sysinsert.sql:16: NOTICE:  Error occurred while
> executing PL/pgSQL function fn_sysmessages_log
> psql:sysinsert.sql:16: NOTICE:  at END of toplevel PL block
> psql:sysinsert.sql:16: ERROR:  control reaches end of trigger procedure
> without RETURN

Look carefully - you don't have a RETURN new/old/null in your function. Take a
look at the online manual (Server Programming, chapter 20) or some of the
samples in Roberto's PostgreSQL cookbook (http://techdocs.postgresql.org)

> function :
> CREATE function fn_sysmessages_log() RETURNS OPAQUE AS '
> BEGIN
> INSERT INTO sysmessages_log
>        (
>        id,
>        user_id,
>        message_date,
>        message_priority,
>        message,
>        status
>        )
> VALUES
>         (
>         NEW.id,
>         NEW.user_id,
>         NEW.message_date,
>         NEW.message_priority,
>         NEW.message,
>         NEW.status
>         );
> END;
> ' LANGUAGE 'plpgsql';

--  Richard Huxton