For example, the title may match an existing entry -- but
the author may be different (e.g., misspelled, or some
"other" author listed on a book having multiple authors, etc.).
Ideally, I would like the database to suspend the INSERT,
ask for confirmation (and "why") and then, either commit
the INSERT or abort it (based on the user's response).
Nearest I can imagine, there's only one ways I can do this:
issue a query that looks for these types of problems and
based on the result, let the *application* prompt the
user for confirmation. Then, *if* confirmed, do the real
INSERT.
Is there a more elegant way?
Well, your application could:
BEGIN TRANSACTION;
Then it would
INSERT INTO... or call a stored procedure. The triggers/stored procedure would
do all what's needed to perform such action, but when it notices something
suspicious it would RAISE (see PLpgSQL) a notice describing the problem(s).
If your user application notices such messages, it issues a message "WARNING:
the message, do you want to continue?" and if user presses yes, you
do COMMIT. Otherwise you do ROLLBACK.
The thing is to use http://www.postgresql.org/docs/8.1/static/plpgsql-errors-and-messages.html
Regards,
Dawid
