Re: Table Function (aka SRF) doc patch - Mailing list pgsql-patches

Patch applied.  Thanks.

---------------------------------------------------------------------------



Bruce Momjian wrote:
>
> Your patch has been added to the PostgreSQL unapplied patches list at:
>
>     http://candle.pha.pa.us/cgi-bin/pgpatches
>
> I will try to apply it within the next 48 hours.
>
> ---------------------------------------------------------------------------
>
>
> Joe Conway wrote:
> > Here's the first doc patch for SRFs. The patch covers general
> > information and SQL language specific info wrt SRFs. I've taken to
> > calling this feature "Table Fuctions" to be consistent with (at least)
> > one well known RDBMS.
> >
> > Note that I mention under the SQL language Table Function section that
> > "Functions returning sets" in query target lists is a deprecated
> > feature, subject to removal in later releases. I think there was general
> > agreement on this, but I thought it was worth pointing out.
> >
> > I still need to submit some C language function documentation, but was
> > hoping to see if any further changes were needed in the Composite and
> > SRF API patch that I sent in earlier. I've started the documentation but
> > will hold of sending in a patch for now on that.
> >
> > If no objections, please apply.
> >
> > Thanks,
> >
> > Joe
> >
> > p.s. any feedback on the SRF regression test patch?
>
> [ text/html is unsupported, treating like TEXT/PLAIN ]
>
> > Index: doc//src/sgml/xfunc.sgml
> > ===================================================================
> > RCS file: /opt/src/cvs/pgsql/doc/src/sgml/xfunc.sgml,v
> > retrieving revision 1.51
> > diff -c -r1.51 xfunc.sgml
> > *** doc//src/sgml/xfunc.sgml    22 Mar 2002 19:20:33 -0000    1.51
> > --- doc//src/sgml/xfunc.sgml    13 Jun 2002 20:30:27 -0000
> > ***************
> > *** 188,193 ****
> > --- 188,194 ----
> >    1
> >   </screen>
> >       </para>
> > +
> >      </sect2>
> >
> >      <sect2>
> > ***************
> > *** 407,427 ****
> >      </sect2>
> >
> >      <sect2>
> > !     <title><acronym>SQL</acronym> Functions Returning Sets</title>
> >
> >       <para>
> > !      As previously mentioned, an SQL function may be declared as
> > !      returning <literal>SETOF <replaceable>sometype</></literal>.
> > !      In this case the function's final <command>SELECT</> query is executed to
> > !      completion, and each row it outputs is returned as an element
> > !      of the set.
> >       </para>
> >
> >       <para>
> > !      Functions returning sets may only be called in the target list
> > !      of a <command>SELECT</> query.  For each row that the <command>SELECT</> generates by itself,
> > !      the function returning set is invoked, and an output row is generated
> > !      for each element of the function's result set.  An example:
> >
> >   <programlisting>
> >   CREATE FUNCTION listchildren(text) RETURNS SETOF text AS
> > --- 408,460 ----
> >      </sect2>
> >
> >      <sect2>
> > !     <title><acronym>SQL</acronym> Table Functions (Functions Returning Sets)</title>
> >
> >       <para>
> > !      A table function is one that may be used in the <command>FROM</command>
> > !      clause of a query. All SQL Language functions may be used in this manner.
> > !      If the function is defined to return a base type, the table function
> > !      produces a one column result set. If the function is defined to
> > !      return <literal>SETOF <replaceable>sometype</></literal>, the table
> > !      function returns multiple rows. To illustrate a SQL table function,
> > !      consider the following, which returns <literal>SETOF</literal> a
> > !      composite type:
> > !
> > ! <programlisting>
> > ! CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
> > ! INSERT INTO foo VALUES(1,1,'Joe');
> > ! INSERT INTO foo VALUES(1,2,'Ed');
> > ! INSERT INTO foo VALUES(2,1,'Mary');
> > ! CREATE FUNCTION getfoo(int) RETURNS setof foo AS '
> > !     SELECT * FROM foo WHERE fooid = $1;
> > ! ' LANGUAGE SQL;
> > ! SELECT * FROM getfoo(1) AS t1;
> > ! </programlisting>
> > !
> > ! <screen>
> > !  fooid | foosubid | fooname
> > ! -------+----------+---------
> > !      1 |        1 | Joe
> > !      1 |        2 | Ed
> > ! (2 rows)
> > ! </screen>
> >       </para>
> >
> >       <para>
> > !      When an SQL function is declared as returning <literal>SETOF
> > !      <replaceable>sometype</></literal>, the function's final
> > !      <command>SELECT</> query is executed to completion, and each row it
> > !      outputs is returned as an element of the set.
> > !     </para>
> > !
> > !     <para>
> > !      Functions returning sets may also currently be called in the target list
> > !      of a <command>SELECT</> query.  For each row that the <command>SELECT</>
> > !      generates by itself, the function returning set is invoked, and an output
> > !      row is generated for each element of the function's result set. Note,
> > !      however, that this capability is deprecated and may be removed in future
> > !      releases. The following is an example function returning a set from the
> > !      target list:
> >
> >   <programlisting>
> >   CREATE FUNCTION listchildren(text) RETURNS SETOF text AS
> > ***************
> > *** 1620,1625 ****
> > --- 1653,1706 ----
> >      </para>
> >     </sect1>
> >
> > +   <sect1 id="xfunc-tablefunctions">
> > +    <title>Table Functions</title>
> > +
> > +    <indexterm zone="xfunc-tablefunctions"><primary>function</></>
> > +
> > +    <para>
> > +     Table functions are functions that produce a set of rows, made up of
> > +     either base (scalar) data types, or composite (multi-column) data types.
> > +     They are used like a table, view, or subselect in the <literal>FROM</>
> > +     clause of a query. Columns returned by table functions may be included in
> > +     <literal>SELECT</>, <literal>JOIN</>, or <literal>WHERE</> clauses in the
> > +     same manner as a table, view, or subselect column.
> > +    </para>
> > +
> > +    <para>
> > +     If a table function returns a base data type, the single result column
> > +     is named for the function. If the function returns a composite type, the
> > +     result columns get the same names as the individual attributes of the type.
> > +    </para>
> > +
> > +    <para>
> > +     A table function may be aliased in the <literal>FROM</> clause, but it also
> > +     may be left unaliased. If a function is used in the FROM clause with no
> > +     alias, the function name is used as the relation name.
> > +    </para>
> > +
> > +    <para>
> > +     Table functions work wherever tables do in <literal>SELECT</> statements.
> > +     For example
> > + <programlisting>
> > + CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
> > + CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
> > + SELECT * FROM getfoo(1) AS t1;
> > + SELECT * FROM foo where foosubid in (select foosubid from getfoo(foo.fooid) z where z.fooid = foo.fooid);
> > + CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
> > + SELECT * FROM vw_getfoo;
> > + </programlisting>
> > +     are all valid statements.
> > +    </para>
> > +
> > +    <para>
> > +     Currently, table functions are supported as SQL language functions
> > +     (<xref linkend="xfunc-sql">) and C language functions
> > +     (<xref linkend="xfunc-c">). See these individual sections for more
> > +     details.
> > +    </para>
> > +
> > +   </sect1>
> >
> >     <sect1 id="xfunc-plhandler">
> >      <title>Procedural Language Handlers</title>
>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026