Re: Set Returning Functions (SRF) - request for patch review and comment - Mailing list pgsql-hackers

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@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.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
> 
> 
>