On Thu, Oct 21, 2004 at 09:26:39PM -0700, David Fetter wrote:
> Folks,
> 
> Here's a patch that adds an example of using SETOF RECORD with the
> needed grammar.

Oops.  Please ignore previous patch.  Here's the right one.

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!
? setof_record.diff
Index: doc/src/sgml/xfunc.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/xfunc.sgml,v
retrieving revision 1.88
diff -u -r1.88 xfunc.sgml
--- doc/src/sgml/xfunc.sgml     20 Sep 2004 22:48:25 -0000      1.88
+++ doc/src/sgml/xfunc.sgml     22 Oct 2004 04:24:53 -0000
@@ -556,6 +556,45 @@
      This happens because <function>listchildren</function> returns an empty set
      for those arguments, so no result rows are generated.
     </para>
+
+    <para>
+    One flexible and powerful thing functions can do is return sets of
+    rows whose type is unknown until runtime.  For this purpose, return
+    <literal>SETOF RECORD</>.  The following function returns all the
+    rows of a given table.
+<screen>
+CREATE FUNCTION from_any_table(TEXT) RETURNS SETOF RECORD AS $$
+DECLARE
+    r RECORD;
+BEGIN
+    FOR r IN EXECUTE 'SELECT * FROM ' || $1 LOOP
+        RETURN NEXT r;
+    END LOOP;
+    RETURN;
+END;
+$$ LANGUAGE plpgsql;
+</screen>
+
+As PostgreSQL does not know in advance the type of columns coming
+back, you must tell it when you invoke the function.
+
+<screen>
+SELECT *
+FROM
+    from_any_table('foo')
+AS
+    foo_tab(fooid INTEGER, foosubid INTEGER, fooname TEXT)
+ORDER BY fooname
+LIMIT 3;
+
+ fooid | foosubid | fooname
+-------+----------+---------
+     2 |        3 | Chimpy
+     1 |        2 | Ed
+     1 |        1 | Joe
+(3 rows)
+</screen>
+    </para>
    </sect2>
 
    <sect2>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to