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

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) #