Skip site navigation (1) Skip section navigation (2)

Re: plpgsql errorcodes

From: "Robert Wimmer" <seppwimmer(at)hotmail(dot)com>
To: andreas_kretschmer(at)despammed(dot)com, pgsql-interfaces(at)postgresql(dot)org
Subject: Re: plpgsql errorcodes
Date: 2004-12-18 10:15:06
Message-ID: BAY10-F26DDF9E08D0D6A8E8D96DAD0A00@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-interfaces
>From: Andreas Kretschmer <andreas_kretschmer(at)despammed(dot)com>
>To: pgsql-interfaces(at)postgresql(dot)org
>Subject: Re: [INTERFACES] plpgsql errorcodes
>Date: Fri, 17 Dec 2004 17:52:00 +0100
>
>begin  "Robert Wimmer" <seppwimmer(at)hotmail(dot)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/


In response to

Responses

pgsql-interfaces by date

Next:From: Tom LaneDate: 2004-12-18 18:02:49
Subject: Re: plpgsql errorcodes
Previous:From: Greg StarkDate: 2004-12-17 18:10:16
Subject: Re: Lost updates vs resumable connections/transactions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group