Thread: AW: [HACKERS] Re: PL/PgSQL discussion
From my experience, it *** is *** impossible. I would be glad if somebody told me different, but to my understanding the doc is wrong here. Andreas >> Currently only 'null' and 'single value'. The executor >> doesn't accept anything else for non-sql language functions. >> PL functions are treated by the executor like 'C' functions. > >Actually what I understood from the docs was thatit is 'terribly complicated' and 'beyond the >scope of this tutorial', but not impossible ;)
Andreas Zeugswetter wrote: > > >From my experience, it *** is *** impossible. I would be glad if somebody > told me different, but to my understanding the doc is wrong here. > > Andreas > > >> Currently only 'null' and 'single value'. The executor > >> doesn't accept anything else for non-sql language functions. > >> PL functions are treated by the executor like 'C' functions. > > > >Actually what I understood from the docs was thatit is 'terribly complicated' and 'beyond the > >scope of this tutorial', but not impossible ;) I have been thinking about that quite a time and when David Gould said 'looks like PL functions want to _be_ SQL functions', my first action was scratching my head. Then I thought it would blow up the parser and executor with things they wheren't designed for and dropped these thoughts. Anyway - thanks David - it was a push into the right direction. Not that I think PL functions should become real SQL functions processed by the backends main parser and executor. But they might become something between 'C' and 'SQL'. The executor could check in ExecMakeFunctionResult() that the function is a PL function (simply by looking at fcache->func.fn_plhandler). So it is possible that the Executor treats PL functions somewhat like postquel_function(). My PL/pgSQL is now at the state, that I can write functions. I'll leave triggers for later and take a look if that all is possible. Would be really nice to enable tuples and sets of tuples as PL return types and finally beeing able to do a DECLARE ret EMP%ROWTYPE; BEGIN ... FOR ret IN SELECT ... LOOP RETURN ret AND RESUME; ENDLOOP; ... END; from PL/pgSQL. Well, returning and resume later might cause some trouble with SPI, but let's see. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
I wrote: > > > Andreas Zeugswetter wrote: > > > > >From my experience, it *** is *** impossible. I would be glad if somebody > > told me different, but to my understanding the doc is wrong here. > > > > Andreas > > > > >> Currently only 'null' and 'single value'. The executor > > >> doesn't accept anything else for non-sql language functions. > > >> PL functions are treated by the executor like 'C' functions. > > > > > >Actually what I understood from the docs was thatit is 'terribly complicated' and 'beyond the > > >scope of this tutorial', but not impossible ;) > > The executor could check in ExecMakeFunctionResult() that the > function is a PL function (simply by looking at > fcache->func.fn_plhandler). So it is possible that the > Executor treats PL functions somewhat like > postquel_function(). That way it might be possible. The call interface to the PL handler must be extended and the PL handler cannot use SPI any more. The memory management of SPI makes it impossible to return from the function and resume later. Leaving the function while connected to SPI would possibly corrupt the SPI stack. And the handling of functions returning tuples is really a mess. The executor calls postquel_function() first to get a tuple table slot, and then again for each attribute used via nested dot expression. It's up to the function itself then to create the correct projection from the functions targetlist. And there must be something in the parser/optimizer too. For an 'sql' function, the fcache has a prepared tuple table slot on the actual call. But for 'C' or 'PL' functions, it is NULL on the call to ExecMakeFunctionResult(). For now, I'll let the PL interface as is and try to finish PL/pgSQL in the trigger area. Someday I'll get back to here and give it a real try to enable tuples and sets as return from functions. That all is far too much work for me right now cause the next 4 weeks I have to do some homework (bought a new house and that's built now). Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #