Skip site navigation (1) Skip section navigation (2)

Re: Prompt User From a pgplsql Function

From: Terry Lee Tucker <terry(at)esc1(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Prompt User From a pgplsql Function
Date: 2005-01-29 14:45:29
Message-ID: 200501290945.29116.terry@esc1.com (view raw or flat)
Thread:
Lists: pgsql-general
In reply to my own question, and to pose a possible solution, and to query the 
list as to whether anybody thinks I'm on the right track, would the following 
excerpt from the documentation be a way of addressing this?

27.9. Notice Processing

Notice and warning messages generated by the server are not returned by the 
query execution functions, since they do not imply failure of the query. 
Instead they are passed to a notice handling function, and execution 
continues normally after the handler returns. The default notice handling 
function prints the message on stderr, but the application can override this 
behavior by supplying its own handling function.

I'm particularly interested in the following:
"they are passed to a notice handling function, and execution continues 
normally after the handler returns." This implies that the function and/or 
trigger will block until the notice handling routine returns. Does anyone 
know if this is true? If I can write my own notice handler, and according to 
the docs, I can, and if I look for some special level of message, like 
warning, or some debug level, could I then accomplish what I need? I realize 
that there will be locks hanging out there while waiting for the input. I 
could put a time on the dialog box, say 15 or 20 seconds, and then answer the 
question programmatically and return. The prompt would only occur 
occasionally, maybe one or two times a day.

Would this be a decent approach to take?

Thanks...

On Saturday 29 January 2005 07:34 am, Terry Lee Tucker saith:
> Greetings List,
>
> I am working on converting a large set of code from a commercial 4gl known
> as Progress to plpgsql. This code defines a rating system for a
> transportation logistics package. In the Progress world, one is able to
> prompt the user for input during the execution of a trigger or a function,
> right in the middle of a transaction. During the rating process, I have a
> couple of situations where I need to prompt the user for a value that lies
> within an acceptable range defined in the rating matrix. The front end to
> this application is X-Windows written in C. All this rating (termed
> AutoRating) desperately needs to be accomplished in plpgsql due to the
> complexity of the record look up and record inserts. Is there any way to
> gather input from a user while in the middle of a transaction from a
> plpgsql function? I think the answer is no; but, maybe some you wizards
> have worked this magic before, or maybe, I've been working at this way too
> long and have missed the obvious.
>
> Any input and/or advice would be greatly appreciated.
>
> Thanks...
> --
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2005-01-29 15:44:13
Subject: Re: [GENERAL] MySQL worm attacks Windows servers
Previous:From: Terry Lee TuckerDate: 2005-01-29 12:34:01
Subject: Prompt User From a pgplsql Function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group