PostgreSQL教程-查询-FROM子句-表函数

时间：2022-01-06 来源：

7.2.1.4. 表函数

表函数是那些生成一个行集合的函数，这个集合可以是由基本数据类型(标量类型)组成，也可以是由复合数据类型(表行)组成。它们的用法类似一个表、视图或者在查询的FROM子句里的子查询。表函数返回的列可以像一个表列、视图或者子查询那样被包含在SELECT、JOIN或WHERE子句里。

也可以使用ROWS FROM语法将平行列返回的结果组合成表函数; 这种情况下结果行的数量是最大一个函数结果的数量，较小的结果会用空值来填充。

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(第 9.18 节)并且使用 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。如果在查询里使用这样的函数，那么我们必须在查询中指定所预期的行结构，这样系统才知道如何分析和规划该查询。这种语法是这样的：

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，因为它可能会被用于任何类型的查询。实际的列集必须在调用它的查询中指定，这样分析器才知道类似*这样的东西应该扩展成什么样子。