Re: functions returning records - Mailing list pgsql-hackers

Karel Zak wrote:
> On Tue, Jun 26, 2001 at 05:11:47PM -0400, Alex Pilosov wrote:
> > I started thinking about Tom's idea to implement functions as table
> > source.
> >
> > To me, it seems that a very few changes are necessary:
> > a) parser must be changed to allow functioncall to be a table_ref
> > (easy)
> >
> > b) when a Query node is generated out of such a call "select * from foo()"
> > it should be almost identical to one generated out of "select * from
> > (select * from foo)" with one distinction: list of query attributes should
> > be completed based on return type of foo().
>
>  For the result from foo() you must somewhere define attributes (names).
> Where? In CREATE FUNCTION statement? Possible must be:
>
>  select name1, name2 from foo() where name1 > 10;
>
>  What returns foo()? ...the pointer to HeapTuple or something like this or
> pointer to some temp table?
>
> > c) executor should support execution of such Query node, properly
> > extracting things out of function's return value and placing them into
> > result attributes.
>
>  d) changes in fmgr
>
>  e) SPI support for table building/filling inside foo()
>
>
>  IMHO very cool and nice feature, but not easy for imlementation.
   Good  questions - must be because I asked them myself before.   :-)
   My idea on that is as follows:
   1.  Adding a new relkind  that  means  'record'.  So  we  use       pg_class,  pg_attribute  and  pg_type as we do
fortables       and views to describe a structure.
 
   2.  A  function  that  RETURNS  SETOF  record/table/view   is       expected  to  return  a  refcursor  (which is
basicallya       portal name - SPI support already in 7.2), who's  tupdesc       matches the structure.
 
   3.  The  Func  node  for such a function invocation will call       the function with the appropriate arguments  to
get the       portal,  receive the tuples with an internal fetch method       one  per  invocation  (I  think  another
destination is       basically enough) and close the portal at the end.
 
   4.  Enhancement  of  the  portal capabilities. A new function       with a tuple descriptor as  argument  creates  a
special       portal  that  simply opens a tuple sink. Another function       stores a tuple there and a third one
rewindsthe sink and       switches  the portal into read mode, so that fetches will       return the tuples again. One
formatof the tuple sink  is       capable  of  backward  moves  too,  so  it'll  be totally       transparent.
 
   5.  Enhancement   of   procedural   languages   that   aren't       implemented  as state machines (currently all of
them)to       use  the  tuple-sink-portals  and  implement  RETURN  AND       RESUME.
 
   This  plan  reuses  alot  of existing code and gains IMHO the   most functionality.  All portals are implicitly
closedat the   end  of  a  transaction.  This  form of internal portal usage   doesn't require explicit transaction
blocks (as  of  current   7.2  tree).  All  the neat buffering, segmenting of the tuple   sink code for materializing
theresult set comes  into  play.   From  the  executors  POV  there  is  no difference between a   function returning a
portalthat's a real SELECT,  collecting   the  data  on the fly, or a function materializing the result   set  first
with RETURN  AND  RESUME.  The  tuple  structure   returned  by  a  function is not only known at parsetime, but   can
beused in other places like for %ROWTYPE in PL/pgSQL.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com