Thread: User-defined functions with NULL values and sets as parameter and/or return type
User-defined functions with NULL values and sets as parameter and/or return type
From
Jeroen van Vianen
Date:
Hi, I have some questions about implementing additional features to the fmgr code: 1. How do I return a NULL value from a function. In fmgr.c I found out that only the C-function with 1 parameter has an additional parameter &isNull which might be used to set the return value to a SQL NULL value. Why not for functions with more paramaters? 2. How do I use sets as a parameter to my code. E.g. in Illustra's API, with which I'm fairly familiar, I wrote a function to send an email to certain people in the following fashion: return sendmail((select email from table where condition), 'from email', 'subject', 'body of email'); Of course it would be possible to do something like this: select sendmail(email, 'from email', 'subject', 'body of email') from table where condition; But this would have a detrimental impact on the server, if it has to open say 400 pipes to /usr/lib/sendmail before this query returns (probably my machine dies on this). The function definition in Illustra would be something like: create function sendmail(setof(text), text, text, text) returns integer as external name '/path/to/sendmail.so'; 3. Can I return sets from a function, e.g. select * from range(1,6) would give me six rows? (Again, in Illustra: create function range(integer, integer) returns setof(integer) as external '/path/to/range.so'). Can this be done using PostgreSQL's API and if not where do I have to start to try implementing this? 4. And finally: I hardly dare to ask this one: setof(user defined type)? Please let me know if you have any information that I can use. If you want me to elaborate some more on Illustra's API (which is quite good on implementing sets except for naming conventions) please let me know. Cheers, Jeroen van Vianen
Re: [HACKERS] User-defined functions with NULL values and sets as parameter and/or return type
From
"Thomas G. Lockhart"
Date:
> 1. How do I return a NULL value from a function. In fmgr.c I found out > that only the C-function with 1 parameter has an additional parameter > &isNull which might be used to set the return value to a SQL NULL value. > Why not for functions with more paramaters? For functions which return a pointer, why not just check for a null pointer? At the moment, returning a null pointer crashes the backend, but it would seem to be fairly easy to fix since it sort-of handles null returns as you found. I was planning on looking at this someday, but am far away from getting to it. It's all yours :) > Please let me know if you have any information that I can use. > If you want me to elaborate some more on Illustra's API (which is quite > good on implementing sets except for naming conventions) please let me > know. I'd be interested in knowing more about Illustra's features. Since we are starting with v6.3 testing and bug fixing, you might want to bring this up again in ~3 weeks to get more of a response... - Tom
Re: [HACKERS] User-defined functions with NULL values and sets as parameter and/or return type
From
jwieck@debis.com (Jan Wieck)
Date:
Hi, I got somewhat familiar with the fmgr code when implementing the new procedural language interface. All of your points are on my personal TODO too :-). But I'm currently very short on time :-(. > > Hi, > > I have some questions about implementing additional features to the fmgr > code: > > 1. How do I return a NULL value from a function. In fmgr.c I found out > that only the C-function with 1 parameter has an additional parameter > &isNull which might be used to set the return value to a SQL NULL value. > Why not for functions with more paramaters? The bool isNull is only passed to fmgr_c(), not to fmgr() which is used in other places of the executor. In addition I would like to be able to identify NULL values passed to the function. Currently the isNull is set to true if one of all arguments is NULL (yes it's an IN/OUT parameter). But read on - below is more on that. > > 2. How do I use sets as a parameter to my code. E.g. in Illustra's API, > with which I'm fairly familiar, I wrote a function to send an email to > certain people in the following fashion: > > return sendmail((select email from table where condition), 'from email', > 'subject', 'body of email'); > > Of course it would be possible to do something like this: > > select > sendmail(email, 'from email', 'subject', 'body of email') > from > table > where > condition; > > But this would have a detrimental impact on the server, if it has to > open say 400 pipes to /usr/lib/sendmail before this query returns > (probably my machine dies on this). > > The function definition in Illustra would be something like: > > create function sendmail(setof(text), text, text, text) returns integer > as external name '/path/to/sendmail.so'; This would be absolutely nice. But currently there is no interface to C functions to pass a set into. Well, this would be kind of a subselect and currently it could be simulated by giving the subselect as a string into the function and having the function executing the select via SPI. Using prepared plans variable arguments for the qualification might be given as separate arguments. > > 3. Can I return sets from a function, e.g. select * from range(1,6) > would give me six rows? > > (Again, in Illustra: create function range(integer, integer) returns > setof(integer) as external '/path/to/range.so'). > > Can this be done using PostgreSQL's API and if not where do I have to > start to try implementing this? The other way round :-) yes - this feature is really missing. But the executor only supports it for SQL language functions right now. The reason for this is that the executor really hacks on the SQL function in this case. The last statement in an SQL function returning a set must be a select. When the executor comes to evaluate such a function it calls the function the first time through and then replaces some internal states of the function cache to return subsequent tuples directly from the resulting scan/join/merge node of the last select in the function. For C or PL functions, this cannot work because they could return data not coming from a relation. The range() function is a really nice little example for this. There isn't any node generated to return the results. It cannot get implemented as a for(...) loop as it must return it's first result on the first call, and the others on subsequent calls. So it will look more like a state machine. Solutions: For the NULL values in and out I think it wouldn't be too hard to implement. A mechanism like CurrentTriggerData used in SPI with a global pointer used by the functions that deal with NULL's would do. A function receiving a set as an argument might get the executor node returning the set passed in and then evaluate that node to get the tuples in. This way, the set passed in could be anything that retrieves tuples (seqscan, indexscan, merge etc.) and the data is presented on the fly, not buffered somewhere like in a temp relation. Returning a set - hmmm - tricky. On the first evaluation of the functions node the executor calls the function than returns another executor node (seqscan on a temp relation or something else). This returned node is then remembered in the functions node and the real return tuples will be received by evaluating the return node. I would really like to start on all of these issues. But before hacking around we should discuss that all in detail. And I think these things should be available in 6.4 someday. > > 4. And finally: I hardly dare to ask this one: setof(user defined type)? Since any relation is a new user defined type, this would work as soon as sets at all get implemented. > > Please let me know if you have any information that I can use. > If you want me to elaborate some more on Illustra's API (which is quite > good on implementing sets except for naming conventions) please let me > know. > > Cheers, > > Jeroen van Vianen > > Until later, 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) #