Possible feature?
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
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 would only allow this text to be output in addition to the standard
text. Else confusion would imho be too great for the unwary admin.
Thus following would be returned:
ERROR 03005 'Cannot insert a duplicate key into a unique index i_test1'
DESCRIPTION 'An employee with a matching Social Security number already exists'
On the other hand, what hinders you from using a "speaking" name for the
constraint ?
postgres=# create table aa (id int, constraint "for Social Security number" unique (id));
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'for Social Security number' for table 'aa'
CREATE
postgres=# insert into aa values (1);
INSERT 23741 1
postgres=# insert into aa values (1);
ERROR: Cannot insert a duplicate key into unique index for Social Security number
postgres=# :-O
Andreas
Import Notes
Resolved by subject fallback
Zeugswetter Andreas SB wrote:
On the other hand, what hinders you from using a "speaking" name for the
constraint ?postgres=# create table aa (id int, constraint "for Social Security number" unique (id));
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'for Social Security number' for table 'aa'
CREATE
postgres=# insert into aa values (1);
INSERT 23741 1
postgres=# insert into aa values (1);
ERROR: Cannot insert a duplicate key into unique index for Social Security number
I might want the message to be in some other language ...
I might even want the language to depend on CURRENT_USER.
-------------
Hannu
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
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'
ENDI'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