8000 > Here's the first doc patch for SRFs. The patch covers general · s-monk/postgres@b2e7a4c · GitHub
[go: up one dir, main page]
Skip to content

Commit b2e7a4c

Browse files
bmomjianbmomjian
committed
> 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. Joe Conway
1 parent 3f90b17 commit b2e7a4c

File tree

1 file changed

+92
-11
lines changed

1 file changed

+92
-11
lines changed

‎doc/src/sgml/xfunc.sgml

Lines changed: 92 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/xfunc.sgml,v 1.51 2002/03/22 19:20:33 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/xfunc.sgml,v 1.52 2002/06/20 16:57:00 momjian Exp $
33
-->
44

55
<chapter id="xfunc">
@@ -188,6 +188,7 @@ SELECT clean_EMP();
188188
1
189189
</screen>
190190
</para>
191+
191192
</sect2>
192193

193194
<sect2>
@@ -407,21 +408,53 @@ SELECT getname(new_emp());
407408
</sect2>
408409

409410
<sect2>
410-
<title><acronym>SQL</acronym> Functions Returning Sets</title>
411+
<title><acronym>SQL</acronym> Table Functions (Functions Returning Sets)</title>
412+
413+
<para>
414+
A table function is one that may be used in the <command>FROM</command>
415+
clause of a query. All SQL Language functions may be used in this manner.
416+
If the function is defined to return a base type, the table function
417+
produces a one column result set. If the function is defined to
418+
return <literal>SETOF <replaceable>sometype</></literal>, the table
419+
function returns multiple rows. To illustrate a SQL table function,
420+
consider the following, which returns <literal>SETOF</literal> a
421+
composite type:
422+
423+
<programlisting>
424+
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
425+
INSERT INTO foo VALUES(1,1,'Joe');
426+
INSERT INTO foo VALUES(1,2,'Ed');
427+
INSERT INTO foo VALUES(2,1,'Mary');
428+
CREATE FUNCTION getfoo(int) RETURNS setof foo AS '
429+
SELECT * FROM foo WHERE fooid = $1;
430+
' LANGUAGE SQL;
431+
SELECT * FROM getfoo(1) AS t1;
432+
</programlisting>
433+
434+
<screen>
435+
fooid | foosubid | fooname
436+
-------+----------+---------
437+
1 | 1 | Joe
438+
1 | 2 | Ed
439+
(2 rows)
440+
</screen>
441+
</para>
411442

412443
<para>
413-
As previously mentioned, an SQL function may be declared as
414-
returning <literal>SETOF <replaceable>sometype</></literal>.
415-
In this case the function's final <command>SELECT</> query is executed to
416-
completion, and each row it outputs is returned as an element
417-
of the set.
444+
When an SQL function is declared as returning <literal>SETOF
445+
<replaceable>sometype</></literal>, the function's final
446+
<command>SELECT</> query is executed to completion, and each row it
447+
outputs is returned as an element of the set.
418448
</para>
419449

420450
<para>
421-
Functions returning sets may only be called in the target list
422-
of a <command>SELECT</> query. For each row that the <command>SELECT</> generates by itself,
423-
the function returning set is invoked, and an output row is generated
424-
for each element of the function's result set. An example:
451+
Functions returning sets may also currently be called in the target list
452+
of a <command>SELECT</> query. For each row that the <command>SELECT</>
453+
generates by itself, the function returning set is invoked, and an output
454+
row is generated for each element of the function's result set. Note,
455+
however, that this capability is deprecated and may be removed in future
456+
releases. The following is an example function returning a set from the
457+
target list:
425458

426459
<programlisting>
427460
CREATE FUNCTION listchildren(text) RETURNS SETOF text AS
@@ -1620,6 +1653,54 @@ CREATE FUNCTION test(int, int) RETURNS int
16201653
</para>
16211654
</sect1>
16221655

1656+
<sect1 id="xfunc-tablefunctions">
1657+
<title>Table Functions</title>
1658+
1659+
<indexterm zone="xfunc-tablefunctions"><primary>function</></>
1660+
1661+
<para>
1662+
Table functions are functions that produce a set of rows, made up of
1663+
either base (scalar) data types, or composite (multi-column) data types.
1664+
They are used like a table, view, or subselect in the <literal>FROM</>
1665+
clause of a query. Columns returned by table functions may be included in
1666+
<literal>SELECT</>, <literal>JOIN</>, or <literal>WHERE</> clauses in the
1667+
same manner as a table, view, or subselect column.
1668+
</para>
1669+
1670+
<para>
1671+
If a table function returns a base data type, the single result column
1672+
is named for the function. If the function returns a composite type, the
1673+
result columns get the same names as the individual attributes of the type.
1674+
</para>
1675+
1676+
<para>
1677+
A table function may be aliased in the <literal>FROM</> clause, but it also
1678+
may be left unaliased. If a function is used in the FROM clause with no
1679+
alias, the function name is used as the relation name.
1680+
</para>
1681+
1682+
<para>
1683+
Table functions work wherever tables do in <literal>SELECT</> statements.
1684+
For example
1685+
<programlisting>
1686+
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
1687+
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
1688+
SELECT * FROM getfoo(1) AS t1;
1689+
SELECT * FROM foo where foosubid in (select foosubid from getfoo(foo.fooid) z where z.fooid = foo.fooid);
1690+
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
1691+
SELECT * FROM vw_getfoo;
1692+
</programlisting>
1693+
are all valid statements.
1694+
</para>
1695+
1696+
<para>
1697+
Currently, table functions are supported as SQL language functions
1698+
(<xref linkend="xfunc-sql">) and C language functions
1699+
(<xref linkend="xfunc-c">). See these individual sections for more
1700+
details.
1701+
</para>
1702+
1703+
</sect1>
16231704

16241705
<sect1 id="xfunc-plhandler">
16251706
<title>Procedural Language Handlers</title>

0 commit comments

Comments
 (0)
0