I've been buried in the backend parser/planner/executor now for the last
2 weeks or so, and I now have a patch for a working implementation of
SRFs as RTEs (i.e. "SELECT tbl.* FROM myfunc() AS tbl"). I think I'm at
a good point to get review and comments. Not everything yet has been
implemented per my proposal (see:
http://fts.postgresql.org/db/mw/msg.html?mid=1077099 ) but most of the
support is in place.
How it currently works:
-----------------------
1. At this point, FROM clause SRFs are used as a row source in a manner
similar to the current API, i.e. one row at a time is produced without
materializing.
2. The SRF may be either marked as returning a set or not. A function
not marked as returning a set simply produces one row.
3. The SRF may either return a base data type (e.g. TEXT) or a composite
data type (e.g. pg_class). If the 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.
4. The SRF *must* be aliased in the FROM clause. This is similar to the
requirement for a subselect used in the FROM clause.
5. example:
test=# 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'
CREATE
test=# INSERT INTO foo VALUES(1,1,'Joe');
INSERT 16693 1
test=# INSERT INTO foo VALUES(1,2,'Ed');
INSERT 16694 1
test=# INSERT INTO foo VALUES(2,1,'Mary');
INSERT 16695 1
test=# CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM
foo WHERE fooid = $1;' LANGUAGE SQL;
CREATE
test=# SELECT * FROM getfoo(1) AS t1;
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
1 | 2 | Ed
(2 rows)
test=# SELECT t1.fooname FROM getfoo(1) AS t1 WHERE t1.foosubid = 1;
fooname
---------
Joe
(1 row)
test=# select * from dblink_get_pkey('foo') as t1;
dblink_get_pkey
-----------------
fooid
foosubid
(2 rows)
What still needs to be done:
----------------------------
1. Add a new table_ref node type - DONE
2. Add support for three modes of operation to RangePortal:
a. Repeated calls -- DONE
b. Materialized results -- partially complete
c. Return query -- I'm starting to wonder how/if this is really
different than a.) above
3. Add support to allow the RangePortal to materialize modes a and c,
if needed for a re-read -- partially complete.
4. Add a WITH keyword to CREATE FUNCTION, allowing SRF mode to be
specified -- not yet started.
Request for help:
-----------------
So far I've tested with SQL and C functions. I will also do some testing
with PLpgSQL functions. I need testing and feedback from users of the
other function PLs.
Review, comments, feedback, etc. are appreciated.
Thanks,
Joe
