Chapter 10. Built-in Table-valued Functions

Built-in table-valued functions are similar to selectable stored procedures. They accept scalar inputs and produce one or more rows as output.

Syntax 

<built-in-table-function> ::=
    <generate-series-function> <correlation-or-recognition>
  | <unlist-table-function> <correlation-or-recognition>
 
<correlation-or-recognition> ::=
  [AS] _correlation-name_ [ ( _column-name_ ) ] ①

①①

This is a simplified form of <correlation-or-recognition> used in chapter Chapter 6, Data Manipulation (DML) Statements.

Table 10.1. General Table-valued Function Parameters
ParameterDescription

correlation-name

Name of the derived table.

Contrary to subquery-based derived tables, the correlation name is required!

column-name

Name of the column of the derived table.

Depending on the use of the derived table, specifying the column name may be necessary. If not specified, the column will have no name and cannot be referenced, but it will receive an alias derived from the function name in the result set of a top-level query.

For user-defined table-valued functions — a.k.a. selectable stored procedures, see Section 5.9.1, “CREATE PROCEDURE”, Section 6.1.3.2, “Selecting FROM a stored procedure”, and Section 7.2.2, “Types of Stored Procedures”.

10.1. General Purpose Table-valued Functions

10.1.1. GENERATE_SERIES()

Returns a table with a series of numbers within a specified interval.

Syntax 

<generate-series> ::=
  <generate-series-function> <correlation-or-recognition>
 
<generate-series-function> ::=
  GENERATE_SERIES(start, limit [, step])
 
<correlation-or-recognition> ::=
  !! See Table-valued functions syntax !!

Table 10.2. GENERATE_SERIES Function Parameters
ParameterDescription

start

Start or first value of the series. Expression of an integer or fixed point data type.

limit

End or upper limit of the series. Expression of an integer or fixed point data type.

step

Non-zero value to add to produce the next value. Default is 1. Expression of an integer or fixed point data type.

The function GENERATE_SERIES returns a table with a column of datatype BIGINT, INT128, NUMERIC(18, x), NUMERIC(38, x), where the precision (or type) is determined by the widest type, and the scale is determined by the maximum scale of the function arguments.

Rules

  • If start greater than limit and a positive step value is specified, an empty table is returned.

  • If start less than limit and a negative step value is specified, an empty table is returned.

  • The series stops once the generated value crosses (exceeds) the limit value.

  • If the step argument is zero, an error is raised.

🛑
Ambiguity with stored procedures called GENERATE_SERIES

GENERATE_SERIES is not a reserved word, and this can result in ambiguity if a stored procedure GENERATE_SERIES exists on the search path. Firebird will use the GENERATE_SERIES built-in function for calls with two or three arguments if the correlation clause (AS …​) is present. For one argument, or four or more arguments, it will call the stored procedure GENERATE_SERIES.

However, if you leave out the correlation clause, it will select from the procedure GENERATE_SERIES, which might be unexpected.

If you need to select from a selectable stored procedure GENERATE_SERIES with two or three arguments and have to provide a correlation, then you need to qualify the procedure with its schema (e.g. PUBLIC.GENERATE_SERIES).

10.1.1.1. GENERATE_SERIES Examples

select N
from generate_series(1, 3) as S(N);
 
select N
from generate_series(3, 1, -1) as S(N);
 
select N
from generate_series(0, 9.9, 0.1) as S(N);
 
select
  dateadd(N minute to timestamp '2025-01-01 12:00') as START_TIME,
  dateadd(N minute to timestamp '2025-01-01 12:00:59.9999') as FINISH_TIME
from generate_series(0, 59) as S(N);

10.1.2. UNLIST()

Splits a string into multiple rows with a single column.

Syntax 

<unlist> ::= <unlist-table-function> <correlation-or-recognition>
 
<unlist-table-function> ::=
  UNLIST ( input [ , separator ] [<unlist-type-conversion>] )
 
<type-conversion> ::= RETURNING <domain_or_non_array_type>
 
<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!
 
<correlation-or-recognition> ::=
  !! See Table-valued functions syntax !!

Table 10.3. UNLIST Function Parameters
ParameterDescription

input

Expression of a string or blob type; input to be split.

separator

Expression of a string or blob type; separator (delimiter) of the input. If not specified, , (comma) is used.

Length cannot exceed 32KiB.

domain_or_non_array_type

Datatype of the output column. Behaves as if Section 8.5.1, “CAST()” is applied with the specified type.

If not specified, VARCHAR(32) is used with the same character set as input. When used in IN UNLIST the type may be dynamically inferred from the left-hand operand.

The UNLIST function does the opposite of the aggregate function Section 9.3.5, “LISTAGG()”: splitting a string into rows.

A shorter form of UNLIST is available for use with Section 4.2.3.2, “IN”.

🛑
Ambiguity with stored procedures called UNLIST

UNLIST is not a reserved word, and this can result in ambiguity if a stored procedure UNLIST exists on the search path. Firebird will use the UNLIST built-in function for calls with one or two arguments if the correlation clause (AS …​) is present. For three or more arguments, it will call the stored procedure UNLIST.

However, if you leave out the correlation clause, it will select from the procedure UNLIST, which might be unexpected.

If you need to select from a selectable stored procedure UNLIST with one or two arguments and have to provide a correlation, then you need to qualify the procedure with its schema (e.g. PUBLIC.UNLIST).

10.1.2.1. UNLIST Examples

select * from unlist('1,2,3,4,5') as U;
 
-- Produce INTEGERs instead of CHAR
select * from unlist('100:200:300:400:500', ':' RETURNING INT) AS U;
 
select U.* from unlist('text1,text2,text3') as U;
 
-- The second and third value produced start with a space
select C0 from unlist('text1, text2, text3') as U(C0);
 
select U.C0 from unlist('text1,text2,text3') as U(C0);
 
-- This does the same as DEPT_NO in ('120', '900')
select EMP_NO from EMPLOYEE
where DEPT_NO in (select * from unlist('120,900') as depts);
 
-- The same but with the short form IN UNLIST
select EMP_NO from EMPLOYEE
where DEPT_NO in unlist('120,900');
 
-- "Column unknown" error as no explict column-name is defined
-- -> default alias UNLIST only exists on output
select UNLIST from unlist('UNLIST,A,S,A') as A;
 
-- "Procedure unknown" error as no correlation-name is defined
select * from unlist('A,B,C')

See alsoSection 9.3.5, “LISTAGG()”