主页 / dba / 问题 / 135378
Accepted
tinlyx
Asked: 2016-04-15 19:46:15 +0800 CST

如何将 RETURNS TABLE 与 PostgreSQL 中的现有表一起使用？

  • 772

从这里的 PostgreSQL 文档：

还有另一种方法可以将函数声明为返回集合，即使用语法 RETURNS TABLE(columns)。...这种表示法是在 SQL 标准的最新版本中指定的，因此可能比使用 SETOF 更便携。

这听起来好像RETURNS TABLE是一种更新、更便携的样式来返回多行。但我不确定这两种语法是否等效。

我想知道我们是否真的可以使用RETURNS TABLE来替换 RETURNS SETOF？

特别是，我还没有弄清楚的一种情况是：如果我们有一个现有的表foo及其关联的复合类型，我们如何在 中使用它RETURNS TABLE？

使用上述链接的示例，可以使用以下内容重写RETURNS TABLE：

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;

到目前为止，我尝试使用RETURNS TABLE (foo.*)and RETURNS TABLE (foo)，但没有成功。

postgresql postgresql-9.5

2 个回答

  1. Best Answer

    PS：抱歉我的评论，我正在开发一个复杂的代码，我有一个小错误，似乎是对“return table”部分的愚蠢 PostgreSQL 限制......我很愚蠢，我没有集中精力解决它，而是使用互联网（搜索引擎把我放在这里）。现在，这个 wiki-answer 是为了帮助其他读者，被搜索引擎调用并被问题的标题所吸引。

    感谢@dezso（是一个正确的答案），请所有读者，您可以编辑这个问题以更具教学性，它是一个 Wiki。

    由于 PostgreSQL-v8 我们可以做到！我们可以返回 EXISTING_TABLE_NAME

    在其指南中，在所有 PostgreSQL 版本中，从pg v8到当前版本，都有一个名为 “SQL Functions as Table Sources”的部分。让我们通过一些简化来重现指南的示例：

    CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe'), (1, 2, 'Ed'), (2, 1, 'Mary');

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

SELECT * FROM getfoo(1);

    它按预期运行，非常完美！

     fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed

    问题“如何将 RETURNS TABLE 与 PostgreSQL 中的现有表一起使用？” 自 pg v8 以来有一个很好的答案...这是我们在过去 15 年中这样做的方式，语法是：
      RETURNS SETOF <EXISTING_TABLE_NAME>     .

    使用子句 TABLE 作为瞬时 CREATE TABLE 用于返回

    @tinlyx 的困惑，在他的问题上解释，是关于使用子句TABLE而不是SETOF......要考虑使用“PostgreSQL 语法逻辑”，我们必须首先记住它RETURN <EXISTING_TABLE_NAME>也是有效的，并且它具有与RETURN <EXISTING_TYPE_NAME>. 自然只返回一行。

    下一步，请记住我们使用 CREATE TABLE 子句 (<tuple_description>) 声明了一个元组，因此，表达“即时定义元组表”的好语法是 RETURN TABLE (<tuple_description>)，它是有意义的return TABLE 类型，类似于数组类型，将返回多个实例（TABLE 是一组元组）。

    下一步，记住我们用子句声明了一个元组CREATE TABLE (<tuple_description>)，因此，表达“瞬时表定义”的好语法是RETURN TABLE (<tuple_description>); 并且返回 Table-type 是有意义的，就像 Array-type 一样，它们返回多个实例（TABLE 是一组元组）。

    PostgreSQL（！）中的“现代事物”是@ZiggyCrueltyfreeZeitgeister 所展示的RETURNS TABLE (LIKE <table_name>)语法。

    CREATE FUNCTION getfoo2(int) RETURNS TABLE (LIKE foo) AS $$ -- working fine!
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM getfoo2(1); -- same result as getfoo(1)

    很多方法可以做到这一点，总结一下：

    显式表名（两种方式）或类型名：

    • RETURNS TABLE (LIKE <table_name>)（现代又好）
    • RETURNS SETOF <table_name>（老但是好）
    • RETURNS SETOF <type_name>（之后 CREATE TYPE <type_name> (<tuple_description>)）

    隐式/通用方式，通过匿名类型：

    • RETURNS SETOF RECORD（通用但有时有问题）
    • （存在的东西？）RETURNS SETOF ROW?

    瞬时表定义：

    • RETURNS TABLE (<tuple_description>)
    • (no RETURNS)OUT在参数列表中使用。

    对于最后一种情况，用我们的例子来说明：
    CREATE FUNCTION getfoo(int, OUT fooid int, OUT foosubid int, OUT fooname text)

    对于动态和/或多态输入，您必须检查此说明。

    最佳实践？

    有很多方法可以做到这一点，那么，有一个“最好的”吗？

    作为语法，我更喜欢使用RETURNS TABLE (LIKE <table_name>)，这是明确的：不要与“隐式记录”混淆，不要担心不兼容......

    对于库管理很重要，DROP TABLE foo CASCADE也会删除该功能：在任何语法（returns table或returns setof）中，PostgreSQL 都会做得很好。

    drop table foo cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to function getfoo(integer)
drop cascades to function getfoo2(integer)
    • 5

  2. 不同的模式在某些情况下是可以互换的，但是是不同的。RETURNS TABLE您从上面的引文中删除的句子中描述了有什么好处：

    这相当于使用一个或多个 OUT 参数加上将函数标记为返回 SETOF 记录（或 SETOF 单个输出参数的类型，视情况而定）。

    这意味着CREATE FUNCTION ... RETURNS TABLE旨在指定自定义返回类型，即它等同于CREATE TYPE xxx_t AS ([fields...,]); CREATE FUNCTION xxx ... RETURNS SETOF xxx_t, 和CREATE FUNCTION xxx ([in params...,] out [fields...,]) RETURNS SETOF RECORD。

    即，函数不能返回某个其他表类型的表：RETURNS SETOF直接用于该表（正如您在问题的片段中所做的那样）。

    • 3

相关问题