pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

PLPGSQL errors
2+ messages / 2 participants
[nested] [flat]

* PLPGSQL errors
@ 2002-10-09 10:06  Rudi Starcevic <rudi@oasis.net.au>
  0 siblings, 1 reply; 2+ messages in thread

From: Rudi Starcevic @ 2002-10-09 10:06 UTC (permalink / raw)
  To: pgsql-sql



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'
)




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: PLPGSQL errors
@ 2002-10-09 10:20  Richard Huxton <dev@archonet.com>
  parent: Rudi Starcevic <rudi@oasis.net.au>
  0 siblings, 0 replies; 2+ messages in thread

From: Richard Huxton @ 2002-10-09 10:20 UTC (permalink / raw)
  To: Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2002-10-09 10:20 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-09 10:06 PLPGSQL errors Rudi Starcevic <rudi@oasis.net.au>
2002-10-09 10:20 ` Richard Huxton <dev@archonet.com>

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