Thread: SRF patch (was Re: [HACKERS] troubleshooting pointers)

Tom Lane wrote:
> Um.  I don't like that; it assumes not only that ExecutorEnd is the only
> kind of callback needed, but also that there is at most one function
> per ExprContext that needs a shutdown callback.  Neither of these
> assumptions hold water IMO.
>
> The design I had in mind was more like this: add to ExprContext a list
> header field pointing to a list of structs along the lines of
>
>     struct exprcontext_callback {
>         struct exprcontext_callback *next;
>         void (*function) (Datum);
>         Datum arg;
>     }
>
> and then call each specified function with given argument during
> FreeExprContext.  Probably ought to be careful to do that in reverse
> order of registration.  We'd also need to invent a RescanExprContext
> operation to call the callbacks during a Rescan.  The use of Datum
> (and not, say, void *) as PG's standard callback arg type was settled on
> some time ago --- originally for on_proc_exit IIRC --- and seems to have
> worked well enough.

Here's the patch, per my post to HACKERS.

It builds cleanly on my dev box, and passes all regression tests.

Thanks,

Joe

Joe Conway <mail@joeconway.com> writes:
> Here's the patch, per my post to HACKERS.
> It builds cleanly on my dev box, and passes all regression tests.

I've committed this with some revisions.  The VIEW cases you were
worried about seem to work now.  I think you'll find that
single-FROM-item cases generally work, and it's time to start worrying
about joins (ie, rescans).

Parameters also need thought.  This should be rejected:

regression=# select * from foo, foot(fooid) z where foo.f2 = z.f2;
server closed the connection unexpectedly

On the other hand, IMHO this should work:

regression=# select * from foo where f2 in
regression-# (select f2 from foot(foo.fooid) z where z.fooid = foo.fooid);
server closed the connection unexpectedly

and here again rescanning is going to be critical.

            regards, tom lane

PS: test case for above:

create table foo(fooid int, f2 int);
insert into foo values(1, 11);
insert into foo values(2, 22);
insert into foo values(1, 111);

create function foot(int) returns setof foo as '
select * from foo where fooid = $1' language sql;

Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>
>>Here's the patch, per my post to HACKERS.
>>It builds cleanly on my dev box, and passes all regression tests.
>
>
> I've committed this with some revisions.  The VIEW cases you were
> worried about seem to work now.  I think you'll find that
> single-FROM-item cases generally work, and it's time to start worrying
> about joins (ie, rescans).

Thanks! I've been offline most of the weekend, but I can get back on
this now. I'll start work on the rescans and test cases below right
away. Were your revisions extensive? Any major misconceptions on my part?

Thanks,

Joe


>
> Parameters also need thought.  This should be rejected:
>
> regression=# select * from foo, foot(fooid) z where foo.f2 = z.f2;
> server closed the connection unexpectedly
>
> On the other hand, IMHO this should work:
>
> regression=# select * from foo where f2 in
> regression-# (select f2 from foot(foo.fooid) z where z.fooid = foo.fooid);
> server closed the connection unexpectedly
>
> and here again rescanning is going to be critical.
>
>             regards, tom lane
>
> PS: test case for above:
>
> create table foo(fooid int, f2 int);
> insert into foo values(1, 11);
> insert into foo values(2, 22);
> insert into foo values(1, 111);
>
> create function foot(int) returns setof foo as '
> select * from foo where fooid = $1' language sql;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Joe Conway <mail@joeconway.com> writes:
> Were your revisions extensive? Any major misconceptions on my part?

I did a fair amount of polishing on the ExprContext callback stuff,
and removed or moved around some node fields that I thought were
unnecessary or in the wrong place.  I also set up proper infrastructure
for cost estimation on function RTEs (though the estimates themselves
are still pretty lame).  Nothing I'd call "major"... more in the
line of stylistic improvements...

            regards, tom lane

Tom Lane wrote:
> I've committed this with some revisions.  The VIEW cases you were
> worried about seem to work now.  I think you'll find that
> single-FROM-item cases generally work, and it's time to start worrying
> about joins (ie, rescans).

Hi Tom,

I've been looking through the SRF patch as-committed, and I think I
understand most of your changes, but I have a question: FunctionNext()
now seems to *always* use a tuplestore instead of conditionally using
the store only for rescans, or if the function was explicitly marked as
PM_MATERIALIZE. Do you still think there should be an option to project
tuples without first storing them, or should we eliminate the notion of
function mode and always materialize?


>
> Parameters also need thought.  This should be rejected:
>
> regression=# select * from foo, foot(fooid) z where foo.f2 = z.f2;
> server closed the connection unexpectedly

I don't understand why this should be rejected, but it does fail for me
also, due to a NULL slot pointer. At what point should it be rejected?


>
> On the other hand, IMHO this should work:
>
> regression=# select * from foo where f2 in
> regression-# (select f2 from foot(foo.fooid) z where z.fooid = foo.fooid);
> server closed the connection unexpectedly

This also fails in (based on a quick look) exactly the same way -- a
NULL slot pointer (econtext->ecxt_scantuple) passed to ExecEvalVar().


BTW, The test cases I was using previously now all pass (copy below).

Thanks,

Joe


DROP TABLE foo;
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');

-- sql, proretset = f, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo
WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM
foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = f, prorettype = c
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE
fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = t, prorettype = c
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo
WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- C, proretset = f, prorettype = b
SELECT * FROM dblink_replace('123456789987654321', '99', 'HelloWorld');
DROP VIEW vw_dblink_replace;
CREATE VIEW vw_dblink_replace AS SELECT * FROM
dblink_replace('123456789987654321', '99', 'HelloWorld');
SELECT * FROM vw_dblink_replace;

-- C, proretset = t, prorettype = b
SELECT dblink_get_pkey FROM dblink_get_pkey('foo');
DROP VIEW vw_dblink_get_pkey;
CREATE VIEW vw_dblink_get_pkey AS SELECT dblink_get_pkey FROM
dblink_get_pkey('foo');
SELECT * FROM vw_dblink_get_pkey;

Joe Conway <mail@joeconway.com> writes:
> I've been looking through the SRF patch as-committed, and I think I
> understand most of your changes, but I have a question: FunctionNext()
> now seems to *always* use a tuplestore instead of conditionally using
> the store only for rescans,

The problem is that as things now stand, you do not know whether you
will be asked to rescan, so you must materialize the result just in
case.

I would like to improve the system so that lower-level plan nodes will
be told whether they need to support rescan; but we aren't there yet,
and I don't think it's the first priority to work on for SRF.  Always
materializing will do for the moment.

> Do you still think there should be an option to project
> tuples without first storing them, or should we eliminate the notion of
> function mode and always materialize?

If the function is going to produce a materialized tupleset to begin
with (because that's convenient for it internally) then there's no value
in having nodeFunctionscan.c make duplicate storage of the tupleset.
We need some way of communicating that fact from the function back to
the plan node ... but again, not first priority.

>> Parameters also need thought.  This should be rejected:
>>
>> regression=# select * from foo, foot(fooid) z where foo.f2 = z.f2;
>> server closed the connection unexpectedly

> I don't understand why this should be rejected, but it does fail for me
> also, due to a NULL slot pointer. At what point should it be rejected?

In the parser.  Ideally, fooid should not even be *visible* while we are
parsing the arguments to the sibling FROM node.  Compare the handling of
variable resolution in JOIN/ON clauses --- the namespace gets
manipulated so that those clauses can't see vars from sibling FROM nodes.

>> On the other hand, IMHO this should work:
>>
>> regression=# select * from foo where f2 in
>> regression-# (select f2 from foot(foo.fooid) z where z.fooid = foo.fooid);
>> server closed the connection unexpectedly

> This also fails in (based on a quick look) exactly the same way -- a
> NULL slot pointer (econtext->ecxt_scantuple) passed to ExecEvalVar().

Right.  This should work, but the Var has to be converted into a Param
referencing the upper-level variable.  I've forgotten right at the
moment where that happens (someplace in the planner) ... but I'll bet
that the someplace doesn't know it needs to process function argument
nodetrees in function RTEs.

            regards, tom lane

Tom Lane wrote:
>>I don't understand why this should be rejected, but it does fail for me
>>also, due to a NULL slot pointer. At what point should it be rejected?
>
>
> In the parser.  Ideally, fooid should not even be *visible* while we are
> parsing the arguments to the sibling FROM node.  Compare the handling of
> variable resolution in JOIN/ON clauses --- the namespace gets
> manipulated so that those clauses can't see vars from sibling FROM nodes.
>

Attached patch takes care of this case. It also passes my previous test
cases (see below). Applies cleanly to CVS tip and passes all regression
tests. Please apply if there are no objections.

I'm still working on the second test case from Tom (the NULL slot
pointer inducing subselect).

Joe

------< tests >-------
test=# \i /opt/src/srf-test.sql
DROP TABLE foo;
DROP
CREATE TABLE foo(fooid int, f2 int);
CREATE
INSERT INTO foo VALUES(1, 11);
INSERT 126218 1
INSERT INTO foo VALUES(2, 22);
INSERT 126219 1
INSERT INTO foo VALUES(1, 111);
INSERT 126220 1
DROP FUNCTION foot(int);
DROP
CREATE FUNCTION foot(int) returns setof foo as 'SELECT * FROM foo WHERE
fooid = $1;' LANGUAGE SQL;
CREATE

-- should fail with ERROR message
select * from foo, foot(fooid) z where foo.f2 = z.f2;
psql:/opt/src/srf-test.sql:10: ERROR:  Function relation in FROM clause
may not refer to other relation, "foo"

DROP TABLE foo;
DROP
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary
key(fooid,foosubid));
psql:/opt/src/srf-test.sql:13: NOTICE:  CREATE TABLE / PRIMARY KEY will
create implicit index 'foo_pkey' for table 'foo'
CREATE
INSERT INTO foo VALUES(1,1,'Joe');
INSERT 126228 1
INSERT INTO foo VALUES(1,2,'Ed');
INSERT 126229 1
INSERT INTO foo VALUES(2,1,'Mary');
INSERT 126230 1

-- sql, proretset = f, prorettype = b
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
  getfoo
--------
       1
(1 row)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
  getfoo
--------
       1
(1 row)


-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo
WHERE fooid = $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
  getfoo
--------
       1
       1
(2 rows)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
  getfoo
--------
       1
       1
(2 rows)


-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM
foo WHERE fooid = $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
  getfoo
--------
  Joe
  Ed
(2 rows)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
  getfoo
--------
  Joe
  Ed
(2 rows)


-- sql, proretset = f, prorettype = c
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE
fooid = $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
  fooid | foosubid | fooname
-------+----------+---------
      1 |        1 | Joe
(1 row)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
  fooid | foosubid | fooname
-------+----------+---------
      1 |        1 | Joe
(1 row)


-- sql, proretset = t, prorettype = c
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo
WHERE fooid = $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
  fooid | foosubid | fooname
-------+----------+---------
      1 |        1 | Joe
      1 |        2 | Ed
(2 rows)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
  fooid | foosubid | fooname
-------+----------+---------
      1 |        1 | Joe
      1 |        2 | Ed
(2 rows)


-- C, proretset = f, prorettype = b
SELECT * FROM dblink_replace('123456789987654321', '99', 'HelloWorld');
        dblink_replace
----------------------------
  12345678HelloWorld87654321
(1 row)

DROP VIEW vw_dblink_replace;
DROP
CREATE VIEW vw_dblink_replace AS SELECT * FROM
dblink_replace('123456789987654321', '99', 'HelloWorld');
CREATE
SELECT * FROM vw_dblink_replace;
        dblink_replace
----------------------------
  12345678HelloWorld87654321
(1 row)


-- C, proretset = t, prorettype = b
SELECT dblink_get_pkey FROM dblink_get_pkey('foo');
  dblink_get_pkey
-----------------
  fooid
  foosubid
(2 rows)

DROP VIEW vw_dblink_get_pkey;
DROP
CREATE VIEW vw_dblink_get_pkey AS SELECT dblink_get_pkey FROM
dblink_get_pkey('foo');
CREATE
SELECT * FROM vw_dblink_get_pkey;
  dblink_get_pkey
-----------------
  fooid
  foosubid
(2 rows)

Index: src/backend/parser/parse_clause.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/parser/parse_clause.c,v
retrieving revision 1.92
diff -c -r1.92 parse_clause.c
*** src/backend/parser/parse_clause.c    12 May 2002 23:43:03 -0000    1.92
--- src/backend/parser/parse_clause.c    15 May 2002 22:55:37 -0000
***************
*** 61,67 ****
  static List *addTargetToSortList(TargetEntry *tle, List *sortlist,
                      List *targetlist, List *opname);
  static bool exprIsInSortList(Node *expr, List *sortList, List *targetList);
!

  /*
   * transformFromClause -
--- 61,70 ----
  static List *addTargetToSortList(TargetEntry *tle, List *sortlist,
                      List *targetlist, List *opname);
  static bool exprIsInSortList(Node *expr, List *sortList, List *targetList);
! static void checkParameterVisibility(ParseState *pstate,
!                                 RangeTblRef *rtr,
!                                 RangeFunction *rangefunc,
!                                 List *containedRels);

  /*
   * transformFromClause -
***************
*** 545,550 ****
--- 548,559 ----

          rtr = transformRangeFunction(pstate, (RangeFunction *) n);
          *containedRels = makeListi1(rtr->rtindex);
+
+         /*
+          * Make sure we've only been given valid parameters, i.e.
+          * we should not see vars from sibling FROM nodes.
+          */
+         checkParameterVisibility(pstate, rtr, (RangeFunction *) n, *containedRels);
          return (Node *) rtr;
      }
      else if (IsA(n, JoinExpr))
***************
*** 1377,1380 ****
--- 1386,1445 ----
              return true;
      }
      return false;
+ }
+
+ /* checkParameterVisibility()
+  *      Make sure we don't try to access vars from
+  *      sibling FROM nodes as RangeFunction parameters.
+  */
+ static void
+ checkParameterVisibility(ParseState *pstate, RangeTblRef *rtr, RangeFunction *rangefunc, List *containedRels)
+ {
+     FuncCall       *funccallnode = (FuncCall *) rangefunc->funccallnode;
+     List           *args = funccallnode->args;
+     List           *save_namespace;
+     List           *clause_varnos,
+                    *l;
+
+     /*
+      * This is a tad tricky, for two reasons.  First, the namespace that
+      * the join expression should see is just the any outer references
+      * from upper pstate levels. So, temporarily set this pstate's namespace
+      * accordingly.  (We need not check for refname conflicts, because
+      * transformFromClauseItem() already did.) NOTE: this code is OK only
+      * because a RangeFunction can't legally alter the namespace by causing
+      * implicit relation refs to be added.
+      */
+     save_namespace = pstate->p_namespace;
+     pstate->p_namespace = makeList1(rtr);
+
+     /* transform the list of arguments */
+     foreach(args, funccallnode->args)
+     {
+         lfirst(args) = transformExpr(pstate, (Node *) lfirst(args));
+
+         /*
+          * Second, we need to check that the ON condition doesn't refer to any
+          * rels outside the input subtrees of the JOIN.  It could do that
+          * despite our hack on the namespace if it uses fully-qualified names.
+          * So, grovel through the transformed clause and make sure there are
+          * no bogus references.  (Outer references are OK, and are ignored
+          * here.)
+          */
+
+         clause_varnos = pull_varnos(lfirst(args));
+         foreach(l, clause_varnos)
+         {
+             int            varno = lfirsti(l);
+
+             if (!intMember(varno, containedRels))
+             {
+                 elog(ERROR, "Function relation in FROM clause may not refer to other relation, \"%s\"",
+                      rt_fetch(varno, pstate->p_rtable)->eref->aliasname);
+             }
+         }
+         freeList(clause_varnos);
+
+     }
+     pstate->p_namespace = save_namespace;
  }

Joe Conway wrote:
 >>
 >
 > Attached patch takes care of this case. It also passes my previous
 > test

Sorry, I just noticed that I did not finish modifying the comments that
were cut-and-pasted from elsewhere. This patch includes better comments.

Joe

Index: src/backend/parser/parse_clause.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/parser/parse_clause.c,v
retrieving revision 1.92
diff -c -r1.92 parse_clause.c
*** src/backend/parser/parse_clause.c    12 May 2002 23:43:03 -0000    1.92
--- src/backend/parser/parse_clause.c    15 May 2002 22:55:37 -0000
***************
*** 61,67 ****
  static List *addTargetToSortList(TargetEntry *tle, List *sortlist,
                      List *targetlist, List *opname);
  static bool exprIsInSortList(Node *expr, List *sortList, List *targetList);
!

  /*
   * transformFromClause -
--- 61,70 ----
  static List *addTargetToSortList(TargetEntry *tle, List *sortlist,
                      List *targetlist, List *opname);
  static bool exprIsInSortList(Node *expr, List *sortList, List *targetList);
! static void checkParameterVisibility(ParseState *pstate,
!                                 RangeTblRef *rtr,
!                                 RangeFunction *rangefunc,
!                                 List *containedRels);

  /*
   * transformFromClause -
***************
*** 545,550 ****
--- 548,559 ----

          rtr = transformRangeFunction(pstate, (RangeFunction *) n);
          *containedRels = makeListi1(rtr->rtindex);
+
+         /*
+          * Make sure we've only been given valid parameters, i.e.
+          * we should not see vars from sibling FROM nodes.
+          */
+         checkParameterVisibility(pstate, rtr, (RangeFunction *) n, *containedRels);
          return (Node *) rtr;
      }
      else if (IsA(n, JoinExpr))
***************
*** 1377,1380 ****
--- 1386,1445 ----
              return true;
      }
      return false;
+ }
+
+ /* checkParameterVisibility()
+  *      Make sure we don't try to access vars from
+  *      sibling FROM nodes as RangeFunction parameters.
+  */
+ static void
+ checkParameterVisibility(ParseState *pstate, RangeTblRef *rtr, RangeFunction *rangefunc, List *containedRels)
+ {
+     FuncCall       *funccallnode = (FuncCall *) rangefunc->funccallnode;
+     List           *args = funccallnode->args;
+     List           *save_namespace;
+     List           *clause_varnos,
+                    *l;
+
+     /*
+      * This is a tad tricky, for two reasons.  First, the namespace that
+      * the function expression should see is just the any outer references
+      * from upper pstate levels. So, temporarily set this pstate's namespace
+      * accordingly.  (We need not check for refname conflicts, because
+      * transformFromClauseItem() already did.) NOTE: this code is OK only
+      * because a RangeFunction can't legally alter the namespace by causing
+      * implicit relation refs to be added.
+      */
+     save_namespace = pstate->p_namespace;
+     pstate->p_namespace = makeList1(rtr);
+
+     /* transform the list of arguments */
+     foreach(args, funccallnode->args)
+     {
+         lfirst(args) = transformExpr(pstate, (Node *) lfirst(args));
+
+         /*
+          * Second, we need to check that the function parameters don't refer
+          * to any other rels.  It could do that despite our hack on the namespace
+          * if it uses fully-qualified names. So, grovel through the transformed
+          * clause and make sure there are no bogus references.  (Outer references
+          * are OK, and are ignored here.)
+          *
+          */
+
+         clause_varnos = pull_varnos(lfirst(args));
+         foreach(l, clause_varnos)
+         {
+             int            varno = lfirsti(l);
+
+             if (!intMember(varno, containedRels))
+             {
+                 elog(ERROR, "Function relation in FROM clause may not refer to other relation, \"%s\"",
+                      rt_fetch(varno, pstate->p_rtable)->eref->aliasname);
+             }
+         }
+         freeList(clause_varnos);
+
+     }
+     pstate->p_namespace = save_namespace;
  }

Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> In the parser.  Ideally, fooid should not even be *visible* while we are
>> parsing the arguments to the sibling FROM node.  Compare the handling of
>> variable resolution in JOIN/ON clauses --- the namespace gets
>> manipulated so that those clauses can't see vars from sibling FROM nodes.

> Attached patch takes care of this case. It also passes my previous test
> cases (see below). Applies cleanly to CVS tip and passes all regression
> tests. Please apply if there are no objections.

I've applied a simplified form of this patch --- it seemed you were
doing it the hard way.  (Possibly I should have recommended
RangeSubselect as a model, not JOIN/ON.  Like RangeSubselect,
RangeFunction doesn't need to allow *any* references to Vars of the
current query level.)

Further digging also revealed that query_tree_walker,
query_tree_mutator, and SS_finalize_plan had been missing out on their
responsibilities to process function-RTE expressions.  With those things
fixed, it appears that outer-level Var references and sub-selects work
as expected in function-RTE expressions.

I am still concerned about whether ExecFunctionReScan works correctly;
if not, the problems would show up in join and subquery situations.
I think the parser and planner stages are in pretty good shape now,
though.  (At least as far as the basic functionality goes.  Having
a smarter materialization policy will take work in the planner.)

It's not too soon to start thinking about documentation and regression
tests for SRFs ...

            regards, tom lane

Tom Lane wrote:
> I am still concerned about whether ExecFunctionReScan works correctly;
> if not, the problems would show up in join and subquery situations.
> I think the parser and planner stages are in pretty good shape now,
> though.  (At least as far as the basic functionality goes.  Having
> a smarter materialization policy will take work in the planner.)
>
Here's a small patch to ExecFunctionReScan. It was clearing
   scanstate->csstate.cstate.cs_ResultTupleSlot
when I think it should have been clearing
   scanstate->csstate.css_ScanTupleSlot

although there is no discernable (at least to me) difference either way.

Joe

Index: src/backend/executor/nodeFunctionscan.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/executor/nodeFunctionscan.c,v
retrieving revision 1.1
diff -c -r1.1 nodeFunctionscan.c
*** src/backend/executor/nodeFunctionscan.c    12 May 2002 20:10:02 -0000    1.1
--- src/backend/executor/nodeFunctionscan.c    19 May 2002 20:11:10 -0000
***************
*** 370,389 ****
  void
  ExecFunctionReScan(FunctionScan *node, ExprContext *exprCtxt, Plan *parent)
  {
!     FunctionScanState  *scanstate;
!
!     /*
!      * get information from node
!      */
!     scanstate = (FunctionScanState *) node->scan.scanstate;
!
!     ExecClearTuple(scanstate->csstate.cstate.cs_ResultTupleSlot);

      /*
       * If we haven't materialized yet, just return.
       */
      if (!scanstate->tuplestorestate)
          return;

      /*
       * Here we have a choice whether to drop the tuplestore (and recompute
--- 370,384 ----
  void
  ExecFunctionReScan(FunctionScan *node, ExprContext *exprCtxt, Plan *parent)
  {
!     FunctionScanState  *scanstate = (FunctionScanState *) node->scan.scanstate;

      /*
       * If we haven't materialized yet, just return.
       */
      if (!scanstate->tuplestorestate)
          return;
+
+     ExecClearTuple(scanstate->csstate.css_ScanTupleSlot);

      /*
       * Here we have a choice whether to drop the tuplestore (and recompute

Joe Conway <mail@joeconway.com> writes:
> Here's a small patch to ExecFunctionReScan. It was clearing
>    scanstate->csstate.cstate.cs_ResultTupleSlot
> when I think it should have been clearing
>    scanstate->csstate.css_ScanTupleSlot

Why do you think that?  To the extent that other rescan routines are
clearing anything, they're clearing ResultTupleSlot.

> although there is no discernable (at least to me) difference either way.

My guess is that it's pretty much a no-op, since the slot will get
cleared and re-used on the next call anyway.

            regards, tom lane

Tom Lane wrote:
 > It's not too soon to start thinking about documentation and
 > regression tests for SRFs ...

Attached is a regression test patch for SRFs. I based it on the test
scripts that I have been using, minus the C function tests and without
calls to random() -- figured random() wouldn't work too well for a
regression test ;-)

Joe

Index: src/test/regress/parallel_schedule
===================================================================
RCS file: /opt/src/cvs/pgsql/src/test/regress/parallel_schedule,v
retrieving revision 1.9
diff -c -r1.9 parallel_schedule
*** src/test/regress/parallel_schedule    5 Apr 2002 11:56:55 -0000    1.9
--- src/test/regress/parallel_schedule    19 May 2002 23:32:21 -0000
***************
*** 74,77 ****
  # The sixth group of parallel test
  # ----------
  # "plpgsql" cannot run concurrently with "rules"
! test: limit plpgsql temp domain
--- 74,77 ----
  # The sixth group of parallel test
  # ----------
  # "plpgsql" cannot run concurrently with "rules"
! test: limit plpgsql temp domain rangefuncs
Index: src/test/regress/serial_schedule
===================================================================
RCS file: /opt/src/cvs/pgsql/src/test/regress/serial_schedule,v
retrieving revision 1.9
diff -c -r1.9 serial_schedule
*** src/test/regress/serial_schedule    5 Apr 2002 11:56:55 -0000    1.9
--- src/test/regress/serial_schedule    19 May 2002 23:32:11 -0000
***************
*** 82,85 ****
  test: plpgsql
  test: temp
  test: domain
!
--- 82,85 ----
  test: plpgsql
  test: temp
  test: domain
! test: rangefuncs
Index: src/test/regress/expected/rangefuncs.out
===================================================================
RCS file: src/test/regress/expected/rangefuncs.out
diff -N src/test/regress/expected/rangefuncs.out
*** /dev/null    1 Jan 1970 00:00:00 -0000
--- src/test/regress/expected/rangefuncs.out    20 May 2002 00:10:24 -0000
***************
*** 0 ****
--- 1,348 ----
+ CREATE TABLE foo2(fooid int, f2 int);
+ INSERT INTO foo2 VALUES(1, 11);
+ INSERT INTO foo2 VALUES(2, 22);
+ INSERT INTO foo2 VALUES(1, 111);
+ CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
+ -- supposed to fail with ERROR
+ select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
+ NOTICE:  Adding missing FROM-clause entry for table "foo2"
+ ERROR:  FROM function expression may not refer to other relations of same query level
+ -- function in subselect
+ select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
+  fooid | f2
+ -------+-----
+      1 |  11
+      1 | 111
+      2 |  22
+ (3 rows)
+
+ -- function in subselect
+ select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2;
+  fooid | f2
+ -------+-----
+      1 |  11
+      1 | 111
+ (2 rows)
+
+ -- function in subselect
+ select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2;
+  fooid | f2
+ -------+-----
+      1 |  11
+      1 | 111
+ (2 rows)
+
+ -- nested functions
+ select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
+  fooid | f2
+ -------+-----
+      1 |  11
+      1 | 111
+ (2 rows)
+
+ CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
+ NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
+ INSERT INTO foo VALUES(1,1,'Joe');
+ INSERT INTO foo VALUES(1,2,'Ed');
+ INSERT INTO foo VALUES(2,1,'Mary');
+ -- sql, proretset = f, prorettype = b
+ CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
+ SELECT * FROM getfoo(1) AS t1;
+  getfoo
+ --------
+       1
+ (1 row)
+
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+  getfoo
+ --------
+       1
+ (1 row)
+
+ -- sql, proretset = t, prorettype = b
+ DROP FUNCTION getfoo(int);
+ CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
+ SELECT * FROM getfoo(1) AS t1;
+  getfoo
+ --------
+       1
+       1
+ (2 rows)
+
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+  getfoo
+ --------
+       1
+       1
+ (2 rows)
+
+ -- sql, proretset = t, prorettype = b
+ DROP FUNCTION getfoo(int);
+ CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
+ SELECT * FROM getfoo(1) AS t1;
+  getfoo
+ --------
+  Joe
+  Ed
+ (2 rows)
+
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+  getfoo
+ --------
+  Joe
+  Ed
+ (2 rows)
+
+ -- sql, proretset = f, prorettype = c
+ DROP FUNCTION getfoo(int);
+ CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
+ SELECT * FROM getfoo(1) AS t1;
+  fooid | foosubid | fooname
+ -------+----------+---------
+      1 |        1 | Joe
+ (1 row)
+
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+  fooid | foosubid | fooname
+ -------+----------+---------
+      1 |        1 | Joe
+ (1 row)
+
+ -- sql, proretset = t, prorettype = c
+ DROP FUNCTION getfoo(int);
+ CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
+ SELECT * FROM getfoo(1) AS t1;
+  fooid | foosubid | fooname
+ -------+----------+---------
+      1 |        1 | Joe
+      1 |        2 | Ed
+ (2 rows)
+
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+  fooid | foosubid | fooname
+ -------+----------+---------
+      1 |        1 | Joe
+      1 |        2 | Ed
+ (2 rows)
+
+ -- plpgsql, proretset = f, prorettype = b
+ DROP FUNCTION getfoo(int);
+ CREATE FUNCTION getfoo(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid =
$1;RETURN fooint; END;' LANGUAGE 'plpgsql'; 
+ SELECT * FROM getfoo(1) AS t1;
+  getfoo
+ --------
+       1
+ (1 row)
+
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+  getfoo
+ --------
+       1
+ (1 row)
+
+ -- plpgsql, proretset = f, prorettype = c
+ DROP FUNCTION getfoo(int);
+ CREATE FUNCTION getfoo(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE
fooid= $1; RETURN footup; END;' LANGUAGE 'plpgsql'; 
+ SELECT * FROM getfoo(1) AS t1;
+  fooid | foosubid | fooname
+ -------+----------+---------
+      1 |        1 | Joe
+ (1 row)
+
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+  fooid | foosubid | fooname
+ -------+----------+---------
+      1 |        1 | Joe
+ (1 row)
+
+ DROP TABLE foo2;
+ DROP FUNCTION foot(int);
+ DROP TABLE foo;
+ DROP FUNCTION getfoo(int);
+ DROP VIEW vw_getfoo;
+ -- Rescan tests --
+ CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
+ NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foorescan_pkey' for table 'foorescan'
+ INSERT INTO foorescan values(5000,1,'abc.5000.1');
+ INSERT INTO foorescan values(5001,1,'abc.5001.1');
+ INSERT INTO foorescan values(5002,1,'abc.5002.1');
+ INSERT INTO foorescan values(5003,1,'abc.5003.1');
+ INSERT INTO foorescan values(5004,1,'abc.5004.1');
+ INSERT INTO foorescan values(5005,1,'abc.5005.1');
+ INSERT INTO foorescan values(5006,1,'abc.5006.1');
+ INSERT INTO foorescan values(5007,1,'abc.5007.1');
+ INSERT INTO foorescan values(5008,1,'abc.5008.1');
+ INSERT INTO foorescan values(5009,1,'abc.5009.1');
+ INSERT INTO foorescan values(5000,2,'abc.5000.2');
+ INSERT INTO foorescan values(5001,2,'abc.5001.2');
+ INSERT INTO foorescan values(5002,2,'abc.5002.2');
+ INSERT INTO foorescan values(5003,2,'abc.5003.2');
+ INSERT INTO foorescan values(5004,2,'abc.5004.2');
+ INSERT INTO foorescan values(5005,2,'abc.5005.2');
+ INSERT INTO foorescan values(5006,2,'abc.5006.2');
+ INSERT INTO foorescan values(5007,2,'abc.5007.2');
+ INSERT INTO foorescan values(5008,2,'abc.5008.2');
+ INSERT INTO foorescan values(5009,2,'abc.5009.2');
+ INSERT INTO foorescan values(5000,3,'abc.5000.3');
+ INSERT INTO foorescan values(5001,3,'abc.5001.3');
+ INSERT INTO foorescan values(5002,3,'abc.5002.3');
+ INSERT INTO foorescan values(5003,3,'abc.5003.3');
+ INSERT INTO foorescan values(5004,3,'abc.5004.3');
+ INSERT INTO foorescan values(5005,3,'abc.5005.3');
+ INSERT INTO foorescan values(5006,3,'abc.5006.3');
+ INSERT INTO foorescan values(5007,3,'abc.5007.3');
+ INSERT INTO foorescan values(5008,3,'abc.5008.3');
+ INSERT INTO foorescan values(5009,3,'abc.5009.3');
+ INSERT INTO foorescan values(5000,4,'abc.5000.4');
+ INSERT INTO foorescan values(5001,4,'abc.5001.4');
+ INSERT INTO foorescan values(5002,4,'abc.5002.4');
+ INSERT INTO foorescan values(5003,4,'abc.5003.4');
+ INSERT INTO foorescan values(5004,4,'abc.5004.4');
+ INSERT INTO foorescan values(5005,4,'abc.5005.4');
+ INSERT INTO foorescan values(5006,4,'abc.5006.4');
+ INSERT INTO foorescan values(5007,4,'abc.5007.4');
+ INSERT INTO foorescan values(5008,4,'abc.5008.4');
+ INSERT INTO foorescan values(5009,4,'abc.5009.4');
+ INSERT INTO foorescan values(5000,5,'abc.5000.5');
+ INSERT INTO foorescan values(5001,5,'abc.5001.5');
+ INSERT INTO foorescan values(5002,5,'abc.5002.5');
+ INSERT INTO foorescan values(5003,5,'abc.5003.5');
+ INSERT INTO foorescan values(5004,5,'abc.5004.5');
+ INSERT INTO foorescan values(5005,5,'abc.5005.5');
+ INSERT INTO foorescan values(5006,5,'abc.5006.5');
+ INSERT INTO foorescan values(5007,5,'abc.5007.5');
+ INSERT INTO foorescan values(5008,5,'abc.5008.5');
+ INSERT INTO foorescan values(5009,5,'abc.5009.5');
+ CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid <
$2;' LANGUAGE SQL; 
+ --invokes ExecFunctionReScan
+ SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2;
+  fooid | foosubid |  fooname
+ -------+----------+------------
+   5002 |        1 | abc.5002.1
+   5002 |        2 | abc.5002.2
+   5002 |        3 | abc.5002.3
+   5002 |        4 | abc.5002.4
+   5002 |        5 | abc.5002.5
+   5003 |        1 | abc.5003.1
+   5003 |        2 | abc.5003.2
+   5003 |        3 | abc.5003.3
+   5003 |        4 | abc.5003.4
+   5003 |        5 | abc.5003.5
+ (10 rows)
+
+ CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004);
+ --invokes ExecFunctionReScan
+ SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2;
+  fooid | foosubid |  fooname
+ -------+----------+------------
+   5002 |        1 | abc.5002.1
+   5002 |        2 | abc.5002.2
+   5002 |        3 | abc.5002.3
+   5002 |        4 | abc.5002.4
+   5002 |        5 | abc.5002.5
+   5003 |        1 | abc.5003.1
+   5003 |        2 | abc.5003.2
+   5003 |        3 | abc.5003.3
+   5003 |        4 | abc.5003.4
+   5003 |        5 | abc.5003.5
+ (10 rows)
+
+ CREATE TABLE barrescan (fooid int primary key);
+ NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'barrescan_pkey' for table 'barrescan'
+ INSERT INTO barrescan values(5003);
+ INSERT INTO barrescan values(5004);
+ INSERT INTO barrescan values(5005);
+ INSERT INTO barrescan values(5006);
+ INSERT INTO barrescan values(5007);
+ INSERT INTO barrescan values(5008);
+ CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL;
+ --invokes ExecFunctionReScan with chgParam != NULL
+ SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM
foorescan(b.fooid))ORDER BY 1,2; 
+  fooid | foosubid |  fooname
+ -------+----------+------------
+   5003 |        1 | abc.5003.1
+   5003 |        2 | abc.5003.2
+   5003 |        3 | abc.5003.3
+   5003 |        4 | abc.5003.4
+   5003 |        5 | abc.5003.5
+   5004 |        1 | abc.5004.1
+   5004 |        2 | abc.5004.2
+   5004 |        3 | abc.5004.3
+   5004 |        4 | abc.5004.4
+   5004 |        5 | abc.5004.5
+   5005 |        1 | abc.5005.1
+   5005 |        2 | abc.5005.2
+   5005 |        3 | abc.5005.3
+   5005 |        4 | abc.5005.4
+   5005 |        5 | abc.5005.5
+   5006 |        1 | abc.5006.1
+   5006 |        2 | abc.5006.2
+   5006 |        3 | abc.5006.3
+   5006 |        4 | abc.5006.4
+   5006 |        5 | abc.5006.5
+   5007 |        1 | abc.5007.1
+   5007 |        2 | abc.5007.2
+   5007 |        3 | abc.5007.3
+   5007 |        4 | abc.5007.4
+   5007 |        5 | abc.5007.5
+   5008 |        1 | abc.5008.1
+   5008 |        2 | abc.5008.2
+   5008 |        3 | abc.5008.3
+   5008 |        4 | abc.5008.4
+   5008 |        5 | abc.5008.5
+ (30 rows)
+
+ SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid
FROMfoorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; 
+  fooid | max
+ -------+-----
+   5003 |   5
+   5004 |   5
+   5005 |   5
+   5006 |   5
+   5007 |   5
+   5008 |   5
+ (6 rows)
+
+ CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid
FROMfoorescan(b.fooid)) ORDER BY 1,2; 
+ SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;
+  fooid | foosubid |  fooname
+ -------+----------+------------
+   5004 |        1 | abc.5004.1
+   5004 |        2 | abc.5004.2
+   5004 |        3 | abc.5004.3
+   5004 |        4 | abc.5004.4
+   5004 |        5 | abc.5004.5
+ (5 rows)
+
+ CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid =
b.fooidAND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; 
+ SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;
+  fooid | maxsubid
+ -------+----------
+   5003 |        5
+   5004 |        5
+   5005 |        5
+   5006 |        5
+   5007 |        5
+   5008 |        5
+ (6 rows)
+
+ DROP TABLE foorescan;
+ DROP FUNCTION foorescan(int,int);
+ DROP VIEW vw_foorescan;
+ DROP TABLE barrescan;
+ DROP FUNCTION foorescan(int);
+ DROP VIEW fooview1;
+ DROP VIEW fooview2;
Index: src/test/regress/sql/rangefuncs.sql
===================================================================
RCS file: src/test/regress/sql/rangefuncs.sql
diff -N src/test/regress/sql/rangefuncs.sql
*** /dev/null    1 Jan 1970 00:00:00 -0000
--- src/test/regress/sql/rangefuncs.sql    19 May 2002 23:52:46 -0000
***************
*** 0 ****
--- 1,181 ----
+ CREATE TABLE foo2(fooid int, f2 int);
+ INSERT INTO foo2 VALUES(1, 11);
+ INSERT INTO foo2 VALUES(2, 22);
+ INSERT INTO foo2 VALUES(1, 111);
+
+ CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
+
+ -- supposed to fail with ERROR
+ select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
+
+ -- function in subselect
+ select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
+
+ -- function in subselect
+ select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2;
+
+ -- function in subselect
+ select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2;
+
+ -- nested functions
+ select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
+
+ 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');
+
+ -- sql, proretset = f, prorettype = b
+ CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
+ SELECT * FROM getfoo(1) AS t1;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ -- sql, proretset = t, prorettype = b
+ DROP FUNCTION getfoo(int);
+ CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
+ SELECT * FROM getfoo(1) AS t1;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ -- sql, proretset = t, prorettype = b
+ DROP FUNCTION getfoo(int);
+ CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
+ SELECT * FROM getfoo(1) AS t1;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ -- sql, proretset = f, prorettype = c
+ DROP FUNCTION getfoo(int);
+ CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
+ SELECT * FROM getfoo(1) AS t1;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ -- sql, proretset = t, prorettype = c
+ DROP FUNCTION getfoo(int);
+ CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
+ SELECT * FROM getfoo(1) AS t1;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ -- plpgsql, proretset = f, prorettype = b
+ DROP FUNCTION getfoo(int);
+ CREATE FUNCTION getfoo(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid =
$1;RETURN fooint; END;' LANGUAGE 'plpgsql'; 
+ SELECT * FROM getfoo(1) AS t1;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ -- plpgsql, proretset = f, prorettype = c
+ DROP FUNCTION getfoo(int);
+ CREATE FUNCTION getfoo(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE
fooid= $1; RETURN footup; END;' LANGUAGE 'plpgsql'; 
+ SELECT * FROM getfoo(1) AS t1;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ DROP TABLE foo2;
+ DROP FUNCTION foot(int);
+ DROP TABLE foo;
+ DROP FUNCTION getfoo(int);
+ DROP VIEW vw_getfoo;
+
+ -- Rescan tests --
+ CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
+ INSERT INTO foorescan values(5000,1,'abc.5000.1');
+ INSERT INTO foorescan values(5001,1,'abc.5001.1');
+ INSERT INTO foorescan values(5002,1,'abc.5002.1');
+ INSERT INTO foorescan values(5003,1,'abc.5003.1');
+ INSERT INTO foorescan values(5004,1,'abc.5004.1');
+ INSERT INTO foorescan values(5005,1,'abc.5005.1');
+ INSERT INTO foorescan values(5006,1,'abc.5006.1');
+ INSERT INTO foorescan values(5007,1,'abc.5007.1');
+ INSERT INTO foorescan values(5008,1,'abc.5008.1');
+ INSERT INTO foorescan values(5009,1,'abc.5009.1');
+
+ INSERT INTO foorescan values(5000,2,'abc.5000.2');
+ INSERT INTO foorescan values(5001,2,'abc.5001.2');
+ INSERT INTO foorescan values(5002,2,'abc.5002.2');
+ INSERT INTO foorescan values(5003,2,'abc.5003.2');
+ INSERT INTO foorescan values(5004,2,'abc.5004.2');
+ INSERT INTO foorescan values(5005,2,'abc.5005.2');
+ INSERT INTO foorescan values(5006,2,'abc.5006.2');
+ INSERT INTO foorescan values(5007,2,'abc.5007.2');
+ INSERT INTO foorescan values(5008,2,'abc.5008.2');
+ INSERT INTO foorescan values(5009,2,'abc.5009.2');
+
+ INSERT INTO foorescan values(5000,3,'abc.5000.3');
+ INSERT INTO foorescan values(5001,3,'abc.5001.3');
+ INSERT INTO foorescan values(5002,3,'abc.5002.3');
+ INSERT INTO foorescan values(5003,3,'abc.5003.3');
+ INSERT INTO foorescan values(5004,3,'abc.5004.3');
+ INSERT INTO foorescan values(5005,3,'abc.5005.3');
+ INSERT INTO foorescan values(5006,3,'abc.5006.3');
+ INSERT INTO foorescan values(5007,3,'abc.5007.3');
+ INSERT INTO foorescan values(5008,3,'abc.5008.3');
+ INSERT INTO foorescan values(5009,3,'abc.5009.3');
+
+ INSERT INTO foorescan values(5000,4,'abc.5000.4');
+ INSERT INTO foorescan values(5001,4,'abc.5001.4');
+ INSERT INTO foorescan values(5002,4,'abc.5002.4');
+ INSERT INTO foorescan values(5003,4,'abc.5003.4');
+ INSERT INTO foorescan values(5004,4,'abc.5004.4');
+ INSERT INTO foorescan values(5005,4,'abc.5005.4');
+ INSERT INTO foorescan values(5006,4,'abc.5006.4');
+ INSERT INTO foorescan values(5007,4,'abc.5007.4');
+ INSERT INTO foorescan values(5008,4,'abc.5008.4');
+ INSERT INTO foorescan values(5009,4,'abc.5009.4');
+
+ INSERT INTO foorescan values(5000,5,'abc.5000.5');
+ INSERT INTO foorescan values(5001,5,'abc.5001.5');
+ INSERT INTO foorescan values(5002,5,'abc.5002.5');
+ INSERT INTO foorescan values(5003,5,'abc.5003.5');
+ INSERT INTO foorescan values(5004,5,'abc.5004.5');
+ INSERT INTO foorescan values(5005,5,'abc.5005.5');
+ INSERT INTO foorescan values(5006,5,'abc.5006.5');
+ INSERT INTO foorescan values(5007,5,'abc.5007.5');
+ INSERT INTO foorescan values(5008,5,'abc.5008.5');
+ INSERT INTO foorescan values(5009,5,'abc.5009.5');
+
+ CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid <
$2;' LANGUAGE SQL; 
+
+ --invokes ExecFunctionReScan
+ SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2;
+
+ CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004);
+
+ --invokes ExecFunctionReScan
+ SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2;
+
+ CREATE TABLE barrescan (fooid int primary key);
+ INSERT INTO barrescan values(5003);
+ INSERT INTO barrescan values(5004);
+ INSERT INTO barrescan values(5005);
+ INSERT INTO barrescan values(5006);
+ INSERT INTO barrescan values(5007);
+ INSERT INTO barrescan values(5008);
+
+ CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL;
+
+ --invokes ExecFunctionReScan with chgParam != NULL
+ SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM
foorescan(b.fooid))ORDER BY 1,2; 
+ SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid
FROMfoorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; 
+
+ CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid
FROMfoorescan(b.fooid)) ORDER BY 1,2; 
+ SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;
+
+ CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid =
b.fooidAND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; 
+ SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;
+
+ DROP TABLE foorescan;
+ DROP FUNCTION foorescan(int,int);
+ DROP VIEW vw_foorescan;
+ DROP TABLE barrescan;
+ DROP FUNCTION foorescan(int);
+ DROP VIEW fooview1;
+ DROP VIEW fooview2;

Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>
>>Here's a small patch to ExecFunctionReScan. It was clearing
>>   scanstate->csstate.cstate.cs_ResultTupleSlot
>>when I think it should have been clearing
>>   scanstate->csstate.css_ScanTupleSlot
>
>
> Why do you think that?  To the extent that other rescan routines are
> clearing anything, they're clearing ResultTupleSlot.

Well, nodeMaterial and nodeSort both clear cs_ResultTupleSlot, but they
also use cs_ResultTupleSlot in ExecMaterial/ExecSort, whereas
FunctionNext uses css_ScanTupleSlot. But as you pointed out, perhaps
it's a noop anyway.

I was having trouble getting everything to work correctly with
FunctionNext using cs_ResultTupleSlot. I guess I don't really understand
the distinction, but I did note that the scan nodes (subqueryscan,
seqscan, etc) used css_ScanTupleSlot, while the materialization nodes
tended to use cs_ResultTupleSlot.

Joe

Joe Conway <mail@joeconway.com> writes:
> I was having trouble getting everything to work correctly with
> FunctionNext using cs_ResultTupleSlot. I guess I don't really understand
> the distinction, but I did note that the scan nodes (subqueryscan,
> seqscan, etc) used css_ScanTupleSlot, while the materialization nodes
> tended to use cs_ResultTupleSlot.

ResultTupleSlot is generally used by plan nodes that do ExecProject;
it holds the tuple formed by ExecProject (ie, the calculated SELECT
targetlist).  ScanTupleSlot is normally the raw input tuple.  For
Functionscan I'd suppose that the scan tuple is the tuple returned
by the function and ResultTupleSlot holds the result of ExecProject.
To see the difference, consider

    SELECT a, b, c+1 FROM foo(33);

where foo returns a tuple (a,b,c,d,e).  The scanned tuple is
(a,b,c,d,e), the projected tuple is (a,b,c+1).

It may well be that rescan could usefully clear both scan and result
tuples, but I don't see the point of making such a change only in
FunctionScan.

            regards, tom lane

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?

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

Patch applied.  Thanks.

---------------------------------------------------------------------------



Bruce Momjian wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@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