From: Rudi Starcevic <rudi@oasis.net.au>
To: pgsql-sql@postgresql.org
Subject: PLPGSQL errors
Date: Wed, 9 Oct 2002 20:06:33 +1000
Message-ID: <20021009100633.M18916@oasis.net.au> (raw)
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_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
);
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'
)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rudi@oasis.net.au
Subject: Re: PLPGSQL errors
In-Reply-To: <20021009100633.M18916@oasis.net.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox