sql - 返回连接结果的 Postgresql 函数

Question

在 postgres 函数(纯 SQL 而非 plpgsql)中，如何返回 table1 和 table2 之间的连接结果？如果函数定义有 returns setof table1 或 returns setof table2 它将不起作用，因为它连接了两个表。如果我执行 returns setof record，我会得到一个错误。

每个表中都有很多列，所以我想避免在 table() 中指定它们中的每一列，因为保持它与架构保持同步将是一场维护噩梦变化。

Answer 1

我看到的有两种方式: View 和复合类型。

Playground :

create table foo(foo_id int primary key, x text);
create table bar(bar_id int primary key, bar_foo_id int references foo(foo_id), y text);
insert into foo values(1, 'a'); insert into bar values(1,1,'b');

您可以创建 View 并使用它代替函数:

create or replace view foobar as select * from foo join bar on foo_id = bar_foo_id;
select * from foobar where x = 'a' and y = 'b';

如果你想把它包装成一个函数然后使用 View 名称作为返回类型:

create function foobar_fn(px text, py text) returns setof foobar language sql as $$
select * from foobar where x like px and y like py $$;

缺点:

• 列名在所有涉及的表中应该是不同的
• 每次更改表结构时都应该重新创建 View

另一种方式——使用复合类型作为返回类型:

create function foobar_fn_1(px text, py text) returns table (tfoo foo, tbar bar) language sql as $$
select foo, bar  from foo join bar on foo_id = bar_foo_id where x like px and y like py $$;

此函数返回两列，其中一列包含 foo 表数据，另一列包含 bar 表数据:

postgres=# select * from foobar_fn_1('a','b');
┌───────┬─────────┐
│ tfoo  │  tbar   │
╞═══════╪═════════╡
│ (1,a) │ (1,1,b) │
└───────┴─────────┘

您可以展开所有字段或单独访问每个字段:

postgres=# select (tfoo).*, (tbar).*, (tfoo).x, (tbar).y from foobar_fn_1('a','b');
┌────────┬───┬────────┬────────────┬───┬───┬───┐
│ foo_id │ x │ bar_id │ bar_foo_id │ y │ x │ y │
╞════════╪═══╪════════╪════════════╪═══╪═══╪═══╡
│      1 │ a │      1 │          1 │ b │ a │ b │
└────────┴───┴────────┴────────────┴───┴───┴───┘

注意列周围的括号。