A Polymorphic Table Function (PTF) is a special (table-) function whose number and types of columns is determined by the values of the arguments that are passed to the function when the function is called.
After a Polymorphic Table Function has been defined, it can be used very similar to an ordinary table, yet parametrizable through a list of function-parameters that are passed to the function.
Because PTFs are semantically used like tables, their invocation occurs in a from clause of a select statement.
Row vs table semantics
A PTF has either row or table semantics.
With row semantics, the input to the function is a single row.
With table semantics, the input to the function are one or more rows.
These rows can optionally be partitioned with a partition by clause or sorted with a order by clause.
Declaration
The following two lines demonstrate how a PTF with row semantics and table semantics are declared:
function ptf_r(tab table[, …]) return table pipelined row polymorphic using <client-interface-package>;
function ptf_t(tab table[, …]) return table pipelined table polymorphic using <client-interface-package>;
Especially note the last point: the PTF does not have an «ordinary» function body because the implementation for the function is provided by the client interface package.
The PTF function declaration can be «stand-alone» (create or replace function) or reside in the same package where also the other functions are located.
Client-interface package
The client-interface package that implements the PTF functionality is required to have at least a describe function.
Optionally, this package specification also comes with one or more of the functions/procedures
open
fetch_rows
close
Calling PTFs
After defining a PTF, for example named peeTeaEff, it might be called like so:
select * from peeTeaEff(dual);
create table tab_xyz (…);
select * from peeTeaEff(tab_xyz);
with w as (
select * from peeTeaEff(tab_xyz);
)
select * from peeTeaEff(w);