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

Wed, 04 Feb 1998 22:53:11 +0100
Jeroen van Vianen <jeroenv@design.nl>
User-defined functions with NULL values and sets as parameter and/or
	return type
> 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


Thu, 05 Feb 1998 02:38:16 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
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@debis.com (Jan Wieck) #



Thu, 5 Feb 1998 13:32:03 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] User-defined functions with NULL values and sets as
	parameter and/or return type