Re: "Upcalls" (sort of) from the database

On 4/6/06, Don Y <pgsql(at)dakotacom(dot)net> wrote:
>
> 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