Oracle: Polymorphic Table functions (PTF)

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

Querying the data dictionary

The value of polymorphic in dba_procedures shows if a PTF has row or table semantics:

select
   impltypename,   -- Name of the package that implements the PTF
   polymorphic     -- TABLE or ROW
from
   user_procedures
where
   object_name = '<ptf-func-name>';

Pass-through vs. for-read columns

The values of pass-through columns go to the result set unchanged.

Only the values of for-read columns can be seen in fetch_rows.

Thus, a column can be both, pass-through and for-read.

Requirements for a polymorphic table function

A PTF is a named PL/SQL function that

has exactly one parameter whose data type is table (see error messages PLS-00773: Table function must have at least one argument of TABLE type and PLS-00766: more than one parameter of TABLE type is not allowed). This table parameter makes the PTF a non-leaf PTF.
zero or more parameters of other data types
returns a table (PLS-00767: polymorphic table function can only return TABLE type)
specifies if it is a function with row or table semantics
a reference to a PL/SQL package that implements the client interface for the PTF implementation of the PTF.

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.

The function defines the shape (column names and types etc.) of the table that the PTF produces and returns it as dbms_tf.describe_t

This function is called by the SQL engine when it parses the query, it cannot be called directly by the user

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

Non-leaf and leaf PTFs

PTFs with a table parameter are called non-leaf PTFs, PTFs without such a parameter leaf PTFs.

Apparently, leaf PTFs are not implemented by Oracle.