postgresql - 如何返回表的行类型加上函数的附加列？

Question

我有一个这样定义的表:

create table users (
  id serial primary key,
  name text,
  email text,
);

...我想编写一个返回形状行的函数:

(
  id integer,
  name text,
  email text,
  some_other_column boolean,
)

我设法通过下面的代码实现了这一点，但我不想重新定义用户表中的列:

create or replace function get_users () 
returns table (
  id integer,
  name text,
  email text,
  some_other_column boolean,
) as $$
    select users.*, true as some_other_column from users;
$$ language sql;

有没有办法通过做这样的事情来动态创建行类型？ (postgres 提示 users.* 的语法错误):

create or replace function get_users () 
returns table (
  users.*, 
  some_other_column boolean
) as $$
    select users.*, true as some_other_column from users;
$$ language sql;

请注意，直接执行以下查询工作正常:

select users.*, true as some_other_column from users;

---

这里的最终目标是最终得到一个可调用函数，如 select * from get_users() 返回的行包括现有表中的列和其他列。我不希望调用者担心究竟如何调用该函数。

我的假设是，由于我可以编写返回动态行的简单 sql，所以我应该能够以某种方式将该 sql 存储在数据库中，以保留返回行的结构。

Answer 1

否。目前没有办法做到这一点(包括第 10 页)。

SQL 是一种严格类型化的语言。创建函数时，必须声明返回类型。要返回一组行集(您可以使用 SELECT * FROM srf() 调用):

• 您可以返回匿名记录(RETURNS SETOF 记录)。但是你必须在每次调用时提供一个列定义列表。

• 您可以返回多态(行)类型(RETURNS SETOF anyelement)。但是您必须提供行类型 ( composite type ) 作为函数的参数，并且需要在系统中注册行类型以某种方式。

  • Refactor a PL/pgSQL function to return the output of various SELECT queries

• 您可以使用 RETURNS SETOFrowtype 显式使用任何已注册的行类型。副作用是函数现在取决于行类型。

• 您可以使用 RETURNS TABLE (...) 临时定义返回的行类型 - 您甚至可以混合行类型 (composite types) 和简单类型。但是一个简单的 SELECT * FROM srf() 不会分解嵌套的行类型——比如 Mabu's answer去演示。

相关:

• Return SETOF rows from PostgreSQL function

这一切归结为:

Is there a way to dynamically create a row type by doing something like this?

不，没有。 SELECT * FROM ... 将从系统目录中检索列定义列表，其中行类型必须在 之前注册，您可以通过这种方式调用该函数。

通常最好在 RETURNS TABLE () 子句中拼出列定义列表。这避免了依赖性。如果您需要快速注册基于现有表的行类型而不拼写其列，您可以创建一个 VIEW - 或者一个 TEMPORARY VIEW 如果它只是用于当前 session :

CREATE TEMP VIEW v_users_plus AS
SELECT *, NULL::boolean AS some_other_column FROM users;

这会在系统中注册一个同名的行类型 (v_users_plus)，就像任何其他表或 View 一样。对于非临时函数，显然需要非临时行类型。