pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql errorcodes
4+ messages / 3 participants
[nested] [flat]
* plpgsql errorcodes
@ 2004-12-16 23:52 Robert Wimmer <seppwimmer@hotmail.com>
2004-12-17 16:52 ` Re: plpgsql errorcodes Andreas Kretschmer <andreas_kretschmer@despammed.com>
0 siblings, 1 reply; 4+ messages in thread
From: Robert Wimmer @ 2004-12-16 23:52 UTC (permalink / raw)
To: pgsql-interfaces
hi,
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
special java or php command on the client side.
the only thing postgreSQL returns - if an error occured - is a textual
message you cant use for telling the user what happend.
one thing i want to know - inside the function - is why postgre stopped
working. something like
num := CAST($1 AS INT);
IF ERROR THEN RETURN -3; END IF; -- "cannot convert to integer"
because i do not want to reenvent the wheel. if there is a conversion
function i want to use it, and i want to know if or why it failed.
besides i have written simple type checking functions doing the following
// php
$ret = pg_call("SELECT checkInteger('abc')");
if ($ret != 0) $msg = pg_call("SELECT getErrorMessage($ret,'german')");
else ...
i can use the same functions in every client program (java, VB, C ..).
but i think it is absurd to do the same thing twice. postgre checks it also
in the CAST
statement. the only thing i want to know is what happend. even a parseable
errorcode
like
ERROR(23890) : 'invalid numeric format'
or
ERROR(100) constraint 'myConstraintName '
would be helpfull
is there ANY possibility to catch errorcodes in plpqsql ?
is there ANY possibility to catch errorcodes otherwise ?
i am using postgreSQL 7.4
thanx
Sepp
_________________________________________________________________
Immer für Sie da. MSN Hotmail. http://www.msn.de/email/webbased/ Jetzt
kostenlos anmelden und überall erreichbar sein!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: plpgsql errorcodes
2004-12-16 23:52 plpgsql errorcodes Robert Wimmer <seppwimmer@hotmail.com>
@ 2004-12-17 16:52 ` Andreas Kretschmer <andreas_kretschmer@despammed.com>
2004-12-18 10:15 ` Re: plpgsql errorcodes Robert Wimmer <seppwimmer@hotmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Kretschmer @ 2004-12-17 16:52 UTC (permalink / raw)
To: pgsql-interfaces
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
--
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: plpgsql errorcodes
2004-12-16 23:52 plpgsql errorcodes Robert Wimmer <seppwimmer@hotmail.com>
2004-12-17 16:52 ` Re: plpgsql errorcodes Andreas Kretschmer <andreas_kretschmer@despammed.com>
@ 2004-12-18 10:15 ` Robert Wimmer <seppwimmer@hotmail.com>
2004-12-18 18:02 ` Re: plpgsql errorcodes Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Robert Wimmer @ 2004-12-18 10:15 UTC (permalink / raw)
To: andreas_kretschmer@despammed.com; pgsql-interfaces
>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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: plpgsql errorcodes
2004-12-16 23:52 plpgsql errorcodes Robert Wimmer <seppwimmer@hotmail.com>
2004-12-17 16:52 ` Re: plpgsql errorcodes Andreas Kretschmer <andreas_kretschmer@despammed.com>
2004-12-18 10:15 ` Re: plpgsql errorcodes Robert Wimmer <seppwimmer@hotmail.com>
@ 2004-12-18 18:02 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-12-18 18:02 UTC (permalink / raw)
To: Robert Wimmer <seppwimmer@hotmail.com>; +Cc: andreas_kretschmer@despammed.com; pgsql-interfaces
"Robert Wimmer" <seppwimmer@hotmail.com> writes:
> my problem is, all plpgsql does is fine. but you do not get any errorcode
> during the function.
I'm afraid you're stuck. At some point we're going to extend RAISE to
allow you to specify a SQLSTATE code, but it didn't get done for 8.0.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-12-18 18:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-16 23:52 plpgsql errorcodes Robert Wimmer <seppwimmer@hotmail.com>
2004-12-17 16:52 ` Andreas Kretschmer <andreas_kretschmer@despammed.com>
2004-12-18 10:15 ` Robert Wimmer <seppwimmer@hotmail.com>
2004-12-18 18:02 ` Tom Lane <tgl@sss.pgh.pa.us>
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