Re: [HACKERS] Set Returning Functions (SRF) - request for patch review and comment
On Monday 06 May 2002 18:51, Joe Conway wrote: (...) Request for help: - So far I've tested with SQL and C functions. (...) Can you post an example of a function in C? (I'm trying out your patch from Friday). Thanks, Ian Barwick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[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
srf.2002.05.05.2.patch.gz
Description: GNU Zip compressed data
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
