Re: Set Returning Functions (SRF) - request for patch review and comment

Feedback: you're a legend!

I'll try to patch my CVS and test it at some point...

Chris

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Joe Conway
> Sent: Tuesday, 7 May 2002 12:51 AM
> To: pgsql-hackers
> Subject: [HACKERS] Set Returning Functions (SRF) - request for patch
> review and comment
>
>
> 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
>
>
>