I am currently designing an application which should be accessible from
different interfaces. For this I like to be using stored procedures to
process the contents of form submissions and dialog screens.
After studying the PG database, it seems to me that I can fulfill the
following requirements.
a) The basic contents of the internal data dictionary can be used to
check incoming fields from on their length and permitted contents.
b) With a little extra work, I should be able to define a table which
can be used to check field contents against field masks.
Together with a table reference, the key/value pairs from the submission
can be checked by a table-driven stored procedure which, depending on
the outcome of the test, can return a row which contains an exit status
and an exit message.
Since I do not really like to duplicate (or rewrite functionality), the
following steps should be carried out by the database itself, which has
the possibilities to define and use them. These steps are all kinds of
constraints which can be defined in the CREATE TABLE statement itself.
The big problem I am facing however, is that when an error is
encountered by the database itself, I do not have any influence anymore
on the error message and the flow of control.
Consider the following definition :
drop table testing;
create table testing (
key_nr serial primary key,
string_value text check (string_value != 'xx'),
int_value integer check (int_value <= 255 and int_value >= 0)
);
And the following SQL statement :
insert into testing (string_value, int_value) values ('xx', 100);
This will yield the following error :
ERROR: new row for relation "testing" violates check constraint
"testing_string_value"
interrupt further processing of the stored procedure, and return
immediately to the client.
Showing a user the previous message does not really give a clue about
what has happened. With some processing it is possible to retrieve the
name of the offending field from this message, but it is impossible to
tell the user what is wrong about the contents that he filled in.
To do this would force the programmer to test and catch all errors, and
translate them to meaningful feedback for the user. Since the raised
error returns to the client, this means that the client is forced to do
one of two things : keep a local translation repository, or call another
stored procedure.
The first solution is error prone due to keeping information about parts
of the database in two different places, the second is essentially a
waste of bandwidth and time, since the problem should have been solved
in the first stored procedure.
Another possibility is of course writing all constraints in stored
procedures and not using the built-in possibilities offered by PG, but
isn't that a waste of my time and a waste of all the work that has been
done by this team ?
What solutions do you use ?
Regards,
Jurgen
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
