Re: [PATCHES] Example of function returning SETOF RECORD

[Tom Lane]

David Fetter <[EMAIL PROTECTED]> writes:
>> Here's a patch that adds an example of using SETOF RECORD with the
>> needed grammar.

I'm having a bit of a problem with this, because it is a plpgsql example
inserted into a section that is solely about SQL-language functions.
Can you adapt it to be an SQL function?  Or put the example into the
plpgsql chapter?

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [PATCHES] Example of function returning SETOF RECORD

[David Fetter]

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 -  1.88
+++ doc/src/sgml/xfunc.sgml 22 Oct 2004 04:24:53 -
@@ -556,6 +556,45 @@
  This happens because listchildren returns an empty set
  for those arguments, so no result rows are generated.
 
+
+
+One flexible and powerful thing functions can do is return sets of
+rows whose type is unknown until runtime.  For this purpose, return
+SETOF RECORD.  The following function returns all the
+rows of a given table.
+
+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;
+
+
+As PostgreSQL does not know in advance the type of columns coming
+back, you must tell it when you invoke the function.
+
+
+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)
+
+

 


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]