Attached are two patches to implement and document anonymous composite

types for Table Functions, as previously proposed on HACKERS. Here is a
brief explanation:

1. Creates a new pg_type typtype: 'p' for pseudo type (currently either
     'b' for base or 'c' for catalog, i.e. a class).

2. Creates new builtin type of typtype='p' named RECORD. This is the
     first of potentially several pseudo types.

3. Modify FROM clause grammer to accept:
     SELECT * FROM my_func() AS m(colname1 type1, colname2 type1, ...)
     where m is the table alias, colname1, etc are the column names, and
     type1, etc are the column types.

4. When typtype == 'p' and the function return type is RECORD, a list
     of column defs is required, and when typtype != 'p', it is
disallowed.

5. A check was added to ensure that the tupdesc provide via the parser
     and the actual return tupdesc match in number and type of
attributes.

When creating a function you can do:
     CREATE FUNCTION foo(text) RETURNS setof RECORD ...

When using it you can do:
     SELECT * from foo(sqlstmt) AS (f1 int, f2 text, f3 timestamp)
       or
     SELECT * from foo(sqlstmt) AS f(f1 int, f2 text, f3 timestamp)
       or
     SELECT * from foo(sqlstmt) f(f1 int, f2 text, f3 timestamp)

Included in the patches are adjustments to the regression test sql and
expected files, and documentation.

p.s.
     This potentially solves (or at least improves) the issue of builtin
     Table Functions. They can be bootstrapped as returning RECORD, and
     we can wrap system views around them with properly specified column
     defs. For example:

     CREATE VIEW pg_settings AS
       SELECT s.name, s.setting
       FROM show_all_settings()AS s(name text, setting text);

     Then we can also add the UPDATE RULE that I previously posted to
     pg_settings, and have pg_settings act like a virtual table, allowing
     settings to be queried and set.


Joe Conway

1 files changed, 75 insertions, 2 deletions

diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 9c203f5e57f..a1970c3e887 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.54 2002/04/23 02:07:16 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.55 2002/08/04 19:48:09 momjian Exp $
 PostgreSQL documentation
 -->
 
@@ -40,6 +40,12 @@ where <replaceable class="PARAMETER">from_item</replaceable> can be:
 ( <replaceable class="PARAMETER">select</replaceable> )
     [ AS ] <replaceable class="PARAMETER">alias</replaceable> [ ( <replaceable class="PARAMETER">column_alias_list</replaceable> ) ]
 |
+<replaceable class="PARAMETER">table_function_name</replaceable> ( [ <replaceable class="parameter">argtype</replaceable> [, ...] ] )
+    [ AS ] <replaceable class="PARAMETER">alias</replaceable> [ ( <replaceable class="PARAMETER">column_alias_list</replaceable> | <replaceable class="PARAMETER">column_definition_list</replaceable> ) ]
+|
+<replaceable class="PARAMETER">table_function_name</replaceable> ( [ <replaceable class="parameter">argtype</replaceable> [, ...] ] )
+    AS ( <replaceable class="PARAMETER">column_definition_list</replaceable> )
+|
 <replaceable class="PARAMETER">from_item</replaceable> [ NATURAL ] <replaceable class="PARAMETER">join_type</replaceable> <replaceable class="PARAMETER">from_item</replaceable>
     [ ON <replaceable class="PARAMETER">join_condition</replaceable> | USING ( <replaceable class="PARAMETER">join_column_list</replaceable> ) ]
   </synopsis>
@@ -82,7 +88,7 @@ where <replaceable class="PARAMETER">from_item</replaceable> can be:
       <term><replaceable class="PARAMETER">from_item</replaceable></term>
       <listitem>
        <para>
-        A table reference, sub-SELECT, or JOIN clause.  See below for details.
+        A table reference, sub-SELECT, table function, or JOIN clause.  See below for details.
        </para>
       </listitem>
      </varlistentry>
@@ -156,6 +162,23 @@ where <replaceable class="PARAMETER">from_item</replaceable> can be:
        </para>
       </listitem>
      </varlistentry>
+
+     <varlistentry>
+      <term><replaceable class="PARAMETER">table function</replaceable></term>
+      <listitem>
+       <para>
+	A table function can appear in the FROM clause.  This acts as though
+	its output were created as a temporary table for the duration of
+	this single SELECT command. An alias may also be used. If an alias is
+	written, a column alias list can also be written to provide	substitute names
+	for one or more columns of the table function. If the table function has been
+	defined as returning the RECORD data type, an alias, or the keyword AS, must
+    also be present, followed by a column definition list in the form
+	( <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [, ... ] ).
+	The column definition list must match the actual number and types returned by the function.
+       </para>
+      </listitem>
+     </varlistentry>
      
      <varlistentry>
       <term><replaceable class="PARAMETER">join_type</replaceable></term>
@@ -381,6 +404,19 @@ where <replaceable class="PARAMETER">from_item</replaceable> can be:
    </para>
 
    <para>
+    A FROM item can be a table function (i.e. a function that returns
+    multiple rows and columns).  When a table function is created, it may
+	be defined to return a named scalar or composite data type (an existing
+	scalar data type, or a table or view name), or it may be defined to return
+	a RECORD data type. When a table function is defined to return RECORD, it
+	must be followed in the FROM clause by an alias, or the keyword AS alone,
+	and then by a parenthesized list of column names and types. This provides
+	a query-time composite type definition. The FROM clause composite type
+	must match the actual composite type returned from the function or an
+	ERROR will be generated.
+   </para>
+
+   <para>
     Finally, a FROM item can be a JOIN clause, which combines two simpler
     FROM items.  (Use parentheses if necessary to determine the order
     of nesting.)
@@ -927,6 +963,43 @@ SELECT actors.name
  Woody Allen
 </programlisting>
   </para>
+
+  <para>
+   This example shows how to use a table function, both with and without
+   a column definition list.
+
+<programlisting>
+distributors:
+ did |     name
+-----+--------------
+ 108 | Westward
+ 111 | Walt Disney
+ 112 | Warner Bros.
+ ...
+
+CREATE FUNCTION distributors(int)
+  RETURNS SETOF distributors AS '
+  SELECT * FROM distributors WHERE did = $1;
+  ' LANGUAGE SQL;
+
+SELECT * FROM distributors(111);
+ did |    name
+-----+-------------
+ 111 | Walt Disney
+(1 row)
+
+CREATE FUNCTION distributors_2(int)
+  RETURNS SETOF RECORD AS '
+  SELECT * FROM distributors WHERE did = $1;
+  ' LANGUAGE SQL;
+
+SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
+ f1  |     f2
+-----+-------------
+ 111 | Walt Disney
+(1 row)
+</programlisting>
+  </para>
  </refsect1>
  
  <refsect1 id="R1-SQL-SELECT-3">