Received: from smtp25.baruch.cuny.edu (smtp25.baruch.cuny.edu [150.210.155.201]) by hub.org (8.9.3/8.9.3) with SMTP id SAA23327 for ; Thu, 27 Jan 2000 18:42:56 -0500 (EST) (envelope-from vince@baruch.cuny.edu) Received: (qmail 23654 invoked by uid 0); 27 Jan 2000 23:35:26 -0000 Received: from newton.baruch.cuny.edu (HELO baruch.cuny.edu) (150.210.155.202) by smtp25.baruch.cuny.edu with SMTP; 27 Jan 2000 23:35:26 -0000 Received: (qmail 32152 invoked by uid 1000); 27 Jan 2000 23:42:55 -0000 Date: Thu, 27 Jan 2000 18:42:54 -0500 From: Vince Gonzalez To: pgsql-sql@postgreSQL.org Subject: Re: [SQL] User-defined error messages/codes Message-ID: <20000127184254.A30658@newton.baruch.cuny.edu> References: <20000127181328.E24586@newton.baruch.cuny.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii In-Reply-To: <20000127181328.E24586@newton.baruch.cuny.edu>; from vince@baruch.cuny.edu on Thu, Jan 27, 2000 at 06:13:28PM -0500 [Yes, I know it's bad style to reply to one's own posts. Oh well.] On Thu, Jan 27, 2000 at 06:13:28PM -0500, Vince Gonzalez wrote: > Is it possible to create user-defined error messages in functions? > For example, I'd like for an update to a table to fail with a message like > 'You need to check your privileges' as opposed to 'Permission Denied'. > I'd like to avoid parsing the messages actually returned by the server, > as these could change from revision to revision. Should I do this by > crafting each function to return an error code that I define, and then > use that code to index a table? I'm interested to hear any solutions > people may have come up with. > > I seem to remember there being some sort of exception facility, but I > can't remember where I may have seen it (or I could be crazy). Man, its funny how posting a question to a mailing list makes it easier to find the answer on your own. :) After re-scanning the programmer's manual, I found info on using RAISE to throw messages: test=> CREATE TABLE "test" ("a" int4); CREATE test=> test=> CREATE FUNCTION "throwit" ( ) RETURNS int4 AS ' test'> DECLARE test'> arecord RECORD; test'> BEGIN test'> select a into arecord from test where a = 5; test'> if not found then test'> raise exception ''five not found''; test'> end if; test'> return 5; test'> END; test'> ' LANGUAGE 'plpgsql'; CREATE test=> test=> INSERT INTO test (a) values (1); INSERT 2168921 1 test=> INSERT INTO test (a) values (2); INSERT 2168922 1 test=> INSERT INTO test (a) values (3); INSERT 2168923 1 test=> INSERT INTO test (a) values (4); INSERT 2168924 1 test=> select throwit(); ERROR: five not found test=> Lovely. Now, I still am not sure how to proceed with my first question, of how to handle user privileges in a way that does not require me to actually attempt an operation to find out if it will succeed. --vince