From: Robert Wimmer <seppwimmer@hotmail.com>
To: andreas_kretschmer@despammed.com
To: pgsql-interfaces@postgresql.org
Subject: Re: plpgsql errorcodes
Date: Sat, 18 Dec 2004 10:15:06 +0000
Message-ID: <BAY10-F26DDF9E08D0D6A8E8D96DAD0A00@phx.gbl> (raw)
In-Reply-To: <gefb92-l16.ln1@kaufbach.delug.de>
>From: Andreas Kretschmer <andreas_kretschmer@despammed.com>
>To: pgsql-interfaces@postgresql.org
>Subject: Re: [INTERFACES] plpgsql errorcodes
>Date: Fri, 17 Dec 2004 17:52:00 +0100
>
>begin "Robert Wimmer" <seppwimmer@hotmail.com> wrote:
> > i want the DB functionality to be done by the server. this means i want
>to
> > write functions like
>
> > CREATE FUNCTION insert_any(....) RETURNS INT AS '
> > BEGIN
>
> > END; '
> > LANGUAGE 'plpgsql'
>
> > returning an errorcode so i can map this errorcode to a message for the
>user
> > of the client. and i want to do this on the server without using any
>
>You can use RAISE to generate a error.
>
>end
>Andreas
hi andreas,
RAISE NOTICE wouldnt help, because it would never be executed if a runtime
error occurs. a short example.
CREATE TABLE dummy (
id SERIAL ...
name VARCHAR(20) ,
code INT CONSTRAINT codeNotUnique UNIQUE,
first DATE);
-- this function controls data before inserting
CREATE FUNCTION insert_dummy(TEXT, TEXT, TEXT) RETURNS INTEGER AS '
DECLARE dat DATE;
BEGIN
dat := CAST($3 AS DATE);
-- if an error occurs execution will be stopped i cant drop any notice
-- i would like todo something like
if ERROR != 0 THEN RETURN -1; END IF; -- cannot convert to date
INSERT INTO dummy(name, code, first) VALUES($1,$2,$3);
if CONSTRAINT-ERROR = codeNotUnique THEN RETURN -2 END IF;
-- you cant do that, you have to do the following
-- the SELECT will be done again internal from psql
SELECT INTO tmp * FROM dummy WHERE code = CAST($2 AS INTEGER);
IF FOUND THEN RETURN -2 END IF;
-- everything is OK
RETURN 0;
END; '
LANGUAGE plpgsql;
my problem is, all plpgsql does is fine. but you do not get any errorcode
during the function.
and outside the function the errormessages cannot be used the generate a
usefull errorcode.
its just some english text.
i hope you can understand what i am talking about,
bye sepp
_________________________________________________________________
Sie wollen unterwegs am Handy Nachrichten von Messenger-Freunden erhalten?
http://www.msn.at/msnmobile/
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-interfaces@postgresql.org
Cc: seppwimmer@hotmail.com, andreas_kretschmer@despammed.com
Subject: Re: plpgsql errorcodes
In-Reply-To: <BAY10-F26DDF9E08D0D6A8E8D96DAD0A00@phx.gbl>
* 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