跳到主要内容
版本:下一版

表函数

表函数是返回一组行的函数,这些行可以由基本数据类型(标量类型)或复合数据类型(表行)组成。表函数的使用方式与查询中 FROM 子句中的表、视图或子查询类似。表函数返回的列可以像表或视图的列一样出现在 SELECTJOINWHERE 子句中。

多个表函数也可以通过 ROWS FROM 语法组合在一起,结果按列并排返回;返回的行数取决于结果最多的那个函数,较短结果的空位将自动填充为 null。

function_call [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]
ROWS FROM( function_call [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

如果指定了 WITH ORDINALITY 子句,函数结果中将额外增加一个类型为 bigint 的列,用于为每一行编号,从 1 开始计数。(这是对 SQL 标准中 UNNEST ... WITH ORDINALITY 语法的推广。)默认情况下,该列的列名为 ordinality,但也可以通过 AS 子句为其指定其他名称。

特殊的表函数 UNNEST 可以接受任意数量的数组参数,并返回相应数量的列,相当于对每个参数分别调用 UNNEST 并通过 ROWS FROM 组合在一起。

UNNEST( array_expression [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

如果未指定表别名 table_alias,函数名将用作表名;对于 ROWS FROM() 语法,默认使用第一个函数的名称作为表名。

如果没有提供列别名:

  • 对于返回基本数据类型的函数,列名默认与函数名相同;
  • 对于返回复合类型的函数,列名将采用复合类型中各个属性的名称。

以下是一些示例:

CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
WHERE foosubid IN (
SELECT foosubid
FROM getfoo(foo.fooid) z
WHERE z.fooid = foo.fooid
);

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

在某些场景下,我们希望表函数能够根据调用方式返回不同的列集。为支持这一需求,可以将表函数声明为返回伪类型 record,且不带 OUT 参数。当在查询中使用此类函数时,必须在查询中显式指定预期的行结构,以便系统能够正确解析和生成执行计划。语法如下:

function_call [AS] alias (column_definition [, ... ])
function_call AS [alias] (column_definition [, ... ])
ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )

当不使用 ROWS FROM() 语法时,column_definition 列表将替代原本可以附加在 FROM 项上的列别名列表,定义中的名称即为列别名。当使用 ROWS FROM() 语法时,可以分别为每个函数指定 column_definition 列表;如果只有一个函数且未使用 WITH ORDINALITY 子句,也可以在 ROWS FROM() 后直接写出 column_definition 列表,替代列别名列表。

来看下面这个示例:

SELECT *
FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';

这个 dblink 函数(来自 dblink 模块)用于执行远程查询。由于可能用于任意查询,因此声明为返回 record 类型。调用该函数时必须指定实际的列结构,以便解析器知道 * 展开为什么内容。

再来看一个使用 ROWS FROM 的示例:

SELECT *
FROM ROWS FROM
(
json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
AS (a INTEGER, b TEXT),
generate_series(1, 3)
) AS x (p, q, s)
ORDER BY p;

这个查询将两个函数组合成一个 FROM 目标。json_to_recordset() 被指定返回两列,第一列为整数类型,第二列为文本类型。generate_series() 的结果直接使用。ORDER BY 子句按整数排序 p 列的值。

输出结果如下:

  p  |  q  | s
-----+-----+---
40 | foo | 1
100 | bar | 2
| | 3