Skip site navigation (1) Skip section navigation (2)

Re: functions returning records

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
Cc: Alex Pilosov <alex(at)pilosoft(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: functions returning records
Date: 2001-06-27 15:05:58
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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 for tables
        and views to describe a structure.

    2.  A  function  that  RETURNS  SETOF  record/table/view   is
        expected  to  return  a  refcursor  (which is basically a
        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 rewinds the sink and
        switches  the portal into read mode, so that fetches will
        return the tuples again. One format of the tuple sink  is
        capable  of  backward  moves  too,  so  it'll  be totally

    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

    This  plan  reuses  alot  of existing code and gains IMHO the
    most functionality.  All portals are implicitly closed at 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 the result set comes  into  play.
    From  the  executors  POV  there  is  no difference between a
    function returning a portal that'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 be used in other places like for %ROWTYPE in PL/pgSQL.



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

Do You Yahoo!?
Get your free address at

In response to


pgsql-hackers by date

Next:From: Peter EisentrautDate: 2001-06-27 15:06:20
Subject: Re: Re: 7.2 items
Previous:From: Peter EisentrautDate: 2001-06-27 15:04:49
Subject: Re: Re: 7.2 items

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group