Re: [GENERAL] Prompt User From a pgplsql Function
No need to worry about visibility problems. Trying to insert a record from the notify receiver sends the application into a tail spin that ends in a core dump. It won't work. I guess there is nothing to do but write all this crap in C. I can prompt the user easy enough, but I can't get anything back to the server. Back to the drawing board :o/ On Saturday 29 January 2005 02:00 pm, Ragnar Hafsta saith: On Sat, 2005-01-29 at 13:21 -0500, Terry Lee Tucker wrote: [on prompting user in the middle of a transaction] Thanks for the reply, Tom. I just had an idea about writting the answer from the client to a table designed for that purpose. It could have a unique key as the pid and a column for storing the answer. When the notice receiver returns, I could have the function check for the answer in the table. I have successfully created my own notice receiver and it works. won't there be visibility problems with this ? gnari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Prompt User From a pgplsql Function
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Prompt User From a pgplsql Function
Terry Lee Tucker [EMAIL PROTECTED] writes: Is there any way to gather input from a user while in the middle of a transaction from a plpgsql function? Hmm ... RAISE NOTICE to send out the prompt, and then COPY from some pre-agreed file to collect the result? (COPY FROM STDIN would be nicer but it doesn't work inside plpgsql, and probably can't be made to work without breaking the FE/BE protocol.) Seems messy and insecure, not to mention it requires the client code to be on the same machine as the database. I think you'd be better off spending the time to restructure your functions, instead. If you were using one of the untrusted languages (plperlu etc) it might be possible to create a temporary side communication channel to talk to the client code, but that seems pretty messy as well. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Prompt User From a pgplsql Function
Thanks for the reply, Tom. I just had an idea about writting the answer from the client to a table designed for that purpose. It could have a unique key as the pid and a column for storing the answer. When the notice receiver returns, I could have the function check for the answer in the table. I have successfully created my own notice receiver and it works. Maybe I can rewrite all this in C when I have more time. The heat is on to get this thing done :o/ On Saturday 29 January 2005 11:09 am, Tom Lane saith: Terry Lee Tucker [EMAIL PROTECTED] writes: Is there any way to gather input from a user while in the middle of a transaction from a plpgsql function? Hmm ... RAISE NOTICE to send out the prompt, and then COPY from some pre-agreed file to collect the result? (COPY FROM STDIN would be nicer but it doesn't work inside plpgsql, and probably can't be made to work without breaking the FE/BE protocol.) Seems messy and insecure, not to mention it requires the client code to be on the same machine as the database. I think you'd be better off spending the time to restructure your functions, instead. If you were using one of the untrusted languages (plperlu etc) it might be possible to create a temporary side communication channel to talk to the client code, but that seems pretty messy as well. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Prompt User From a pgplsql Function
On Sat, 2005-01-29 at 13:21 -0500, Terry Lee Tucker wrote: [on prompting user in the middle of a transaction] Thanks for the reply, Tom. I just had an idea about writting the answer from the client to a table designed for that purpose. It could have a unique key as the pid and a column for storing the answer. When the notice receiver returns, I could have the function check for the answer in the table. I have successfully created my own notice receiver and it works. won't there be visibility problems with this ? gnari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Prompt User From a pgplsql Function
Well, I haven't tried it yet, but I don't believe it will. It will be the same connection in the same transaction. In other triggers, I've done something similiar by inserting a record into a special table called override and have other triggers that fire in the same transaction look for a record in that table in order to make a decision about what course of action to take. All the other triggers are able to see the record. I don't see this as being any different. After I try it, I'll let you know. Thanks for the input... On Saturday 29 January 2005 02:00 pm, Ragnar Hafsta saith: On Sat, 2005-01-29 at 13:21 -0500, Terry Lee Tucker wrote: [on prompting user in the middle of a transaction] Thanks for the reply, Tom. I just had an idea about writting the answer from the client to a table designed for that purpose. It could have a unique key as the pid and a column for storing the answer. When the notice receiver returns, I could have the function check for the answer in the table. I have successfully created my own notice receiver and it works. won't there be visibility problems with this ? gnari ---(end of broadcast)--- TIP 8: explain analyze is your friend
