Proposal: anonymous composite types for Table Functions (aka SRFs) - Mailing list pgsql-hackers

Hello all,

There is a limitation currently with Table Functions in that the return 
tuple type must be known in advance, i.e. you need a pre-defined scalar 
or composite type to use as the function's declared return type.

This doesn't work well for the type of function that needs to return 
different tuple structures on each call that depend on the input 
parameters. Two examples of this are dblink and the crosstab function 
that I recently submitted. In the case of:
    dblink(connection_str, sql_stmt)

what is really needed is for dblink to return a tuple of a type as 
determined dynamically by the input sql statement. Similarly, with:
    crosstab(sql)

you'd like to have the number/type of values columns dependent on the 
number of categories and type of the sql statement value column.

Speaking with Tom Lane the other day (off-list), he suggested a possible 
solution. I have spent some time thinking about his suggestion (and even 
started working on the implementation, though I know that is getting the 
cart before the horse) and would like to propose the following solution 
based on it:

1. Create a new pg_type typtype: 'a' for anonymous (currently either 'b' 
for base or 'c' for catalog, i.e. a class). We should also consider 
whether typtype should be renamed typkind.

2. Create new builtin type of typtype='a' named RECORD

3. Modify FROM clause grammer to accept something like:   SELECT * FROM my_func() AS mtf(colname1 type1, colname2
type1,...)
 
where mtf is the table alias, colname1, etc are the column names, and 
type1, etc are the column types.

4. Currently in the parsing and processing of RangeFunctions there are a 
number of places that must check whether the return type is base or 
composite. These would be changed to also handle (typtype == 'a'). When 
typtype == 'a', a List of column defs would be required, when (typtype 
!= 'a'), it would be disallowed. The column defs would be used in place 
of the information derived from the funcrelid for cases with (typtype == 
'c').

5. A check would be added (probably in nodeFunctionscan.c somewhere) to 
ensure that the coldefs provide via the parser and the actual return 
tuple description match.

Now when creating a function you can do:  CREATE FUNCTION foo(text) RETURNS setof RECORD ...

And when using it you can do, e.g.:  SELECT * from foo(sqlstmt) AS (f1 int, f2 text, f3 timestamp)

This potentially also solves (or at least improves) the issue of builtin 
Table Functions. They can be declared as returning RECORD, and we can 
wrap system views around them with properly specified column defs. For 
example:

CREATE VIEW pg_settings AS SELECT s.name, s.setting FROM show_all_settings()AS s(name text, setting text);

Likewise Neil's pg_locks could do the same.

Then we can also add the UPDATE RULE that I previously posted to 
pg_settings, and have pg_settings act like a virtual table, allowing 
settings to be queried and set.

Comments, omissions, or objections?

Thanks,

Joe