Re: [HACKERS] User-defined functions with NULL values and sets as parameter and/or return type

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(at)debis(dot)com (Jan Wieck) #