Hello.

I was just curious if you guys would accept a feature which would allow
for the generation of non-standard messages for the violation of index,
check, and referential integrity constraints. I understand that Peter
E's proposal regarding error messages would allow clients to determine
in greater detail the cause of an elog(). However, I think it might be
of value to implement something which would allow the user to override
the default message sent by the backend. An example situation would be
like this:

CREATE TABLE employees (
employeeid integer not null,
ssnumber text not null
);

CREATE UNIQUE INDEX i_employees on employees(ssnumber);

MESSAGE ON INDEX i_employees IS 
'An employee with a matching Social Security number already exists';

Then, when the UNIQUE constraint of the index is violated, instead of
the message:

'Cannot insert a duplicate key into a unique index i_test1'

the client application would receive:

'An employee with a matching Social Security number already exists'

The benefit to a feature like this is that each client application
doesn't need to handle the generation of the appropriate error messages
themselves, but instead can rely on the database to do so. In fact, it
wouldn't be too hard to have a SET command to set the client language
(much like CLIENT_ENCODING) that would return the message appropriate
for the language of the client. 

Another example:

CREATE TABLE cars (
model integer not null,
make integer not null,
color text not null
constraint check_color check (color = 'Red' or color = 'Blue')
);

MESSAGE ON CONSTRAINT check_color IS
'Only Red or Blue cars are valid. Please refer to page 12 of the User''s
Guide';

Of course, its quite probable that all of this belongs in each of the
clients, but it seems trivial to do, much like pg_description and
COMMENT ON. This is obviously an informal suggestion to determine if the
idea should be rejected out-of-hand.

Mike Mascari
mascarm@mascari.com

Wed, 11 Jul 2001 07:20:22 -0400
Mike Mascari <mascarm@mascari.com>
Possible feature?
Mike Mascari writes:

> MESSAGE ON INDEX i_employees IS
> 'An employee with a matching Social Security number already exists';
>
> Then, when the UNIQUE constraint of the index is violated, instead of
> the message:
>
> 'Cannot insert a duplicate key into a unique index i_test1'
>
> the client application would receive:
>
> 'An employee with a matching Social Security number already exists'

I think what you're after is

TRY
  BEGIN
    INSERT ...
  END
CATCH SQLCODE 12345 -- made up
  BEGIN
    RAISE 'your message here'
  END

I'm positive people would kill for that kind of feature.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Wed, 11 Jul 2001 17:28:58 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Possible feature?
On Wednesday, 11. July 2001 17:28, you wrote:
> Mike Mascari writes:
> > MESSAGE ON INDEX i_employees IS
> > 'An employee with a matching Social Security number already exists';
> >
> > Then, when the UNIQUE constraint of the index is violated, instead of
> > the message:
> >
> > 'Cannot insert a duplicate key into a unique index i_test1'
> >
> > the client application would receive:
> >
> > 'An employee with a matching Social Security number already exists'
>
> I think what you're after is
>
> TRY
>   BEGIN
>     INSERT ...
>   END
> CATCH SQLCODE 12345 -- made up
>   BEGIN
>     RAISE 'your message here'
>   END
>
> I'm positive people would kill for that kind of feature.

Then we should use this syntax (like Oracle does):

BEGIN
   INSERT ....

EXCEPTION WHEN .... THEN
   RAISE 'your message here'
END


Regards, 
Klaus

-- 
Visit WWWdb at
http://wwwdb.org


Thu, 12 Jul 2001 07:05:56 +0200
Klaus Reger <K.Reger@wwwdb.de>
Re: Possible feature?