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

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