Proposal: anonymous composite types for Table Functions (aka SRFs)

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