Обсуждение: 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

> 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.

...

> 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)

Why is there the requirement to declare the type at SELECT time at all?  Why
not just take what you get when you run the function?

Chris

Christopher Kings-Lynne wrote:
> Why is there the requirement to declare the type at SELECT time at all?  Why
> not just take what you get when you run the function?

The column names and types are determined in the parser, and used in the 
planner, optimizer, and executor. I'm not sure how the backend could 
plan a join or a where criteria otherwise.

Remember that the function has to look just like a table or a subselect 
(i.e a RangeVar). With a table, the column names and types are 
predefined. With a subselect, parsing it yields the same information. 
With a table function, we need some way of providing it -- i.e. either 
with a predefined type, or now with a definition right in the FROM clause.

Joe

> > 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)
>
> Why is there the requirement to declare the type at SELECT
> time at all?  Why
> not just take what you get when you run the function?

Yea, that would imho be ultra cool, but I guess the parser/planner must already
know the expected column names and types to resolve conflicts, do a reasonable
plan and use the correct type conversions.

Maybe the AS (...) could be optional, and if left out, the executor would need
to abort iff duplicate colnames (from a joined table) or non binary compatible
conversions would be involved. A "select * from func();" would then always work,
but if you add "where x=5" the executor might need to abort.
Looks like a lot of work though.

Andreas

> The column names and types are determined in the parser, and used in the
> planner, optimizer, and executor. I'm not sure how the backend could
> plan a join or a where criteria otherwise.
>
> Remember that the function has to look just like a table or a subselect
> (i.e a RangeVar). With a table, the column names and types are
> predefined. With a subselect, parsing it yields the same information.
> With a table function, we need some way of providing it -- i.e. either
> with a predefined type, or now with a definition right in the FROM clause.

Or you could "parse" the function by retrieving the first row from the it
and assuming that that's the function definition?

Chris

Christopher Kings-Lynne wrote:
> Or you could "parse" the function by retrieving the first row from the it
> and assuming that that's the function definition?
> 

There are a number of reasons why I don't think this is workable, but 
foremost, what happens if the function has side-effects, i.e. actually 
alters data somehow?

Joe