Thread: plpgsql errorcodes

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!

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° ;-)

>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
liketodo 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
todo the following -- the SELECT will be done again internal from psql SELECT INTO tmp * FROM dummy WHERE code =
CAST($2AS 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/

"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