sql - plpgsql - 通过返回结果集自动检测列类型

Question

我有 Postgresql 8.4

我有一个用户表

user_id INT,
user_name VARCHAR(255),
user_email VARCHAR(255),
user_salt VARCHAR(255)

和 2 个函数:

一个带有 SETOF 的:

CREATE FUNCTION test ()
  RETURNS SETOF "user"
AS
  $BODY$
  BEGIN
    RETURN QUERY SELECT
      *
    FROM
      "user";
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

一个带有 TABLE 的:

CREATE FUNCTION test ()
  RETURNS TABLE (id INT, name VARCHAR, email VARCHAR)
AS
  $BODY$
  BEGIN
    RETURN QUERY SELECT
      "user".user_id, "user".user_name, "user".user_email
    FROM
      "user";
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

通过 SETOF 自动填充列类型，但我无法设置列名以及在结果中选择哪些列。通过 TABLE 我可以切断 user_ 前缀并设置准确的列名，但我必须手动设置列类型。

是否可以兼得两者的优点？

Answer 1

PostgreSQL(和一般的 SQL)中的类型处理是严格的。定义函数的 RETURN 类型可能很棘手。
有使用普通 SQL 的简单解决方案:

选择并重命名所有列:

SELECT * FROM t AS t(id, name, email, salt);

选择并重命名一些列:

SELECT user_id AS id, user_name AS name FROM t;

结合功能

如果您出于某种原因需要服务器端功能，您仍然可以将这些 SQL 功能与一个功能结合起来。给定一个表 t 就像在你的问题中定义的那样，这个简单的函数:

CREATE OR REPLACE FUNCTION f_test()
  RETURNS SETOF t AS
$func$
SELECT * FROM t  -- do stuff here
$func$ LANGUAGE sql STABLE;

仅重命名列(无需提供类型):

SELECT * FROM f_test() t(id, name, email, salt)

选择并重命名一些列:

SELECT user_id AS id, user_name AS name FROM f_test() t;

您可以将其与不同表上的各种不同函数结合使用:
Refactor a PL/pgSQL function to return the output of various SELECT queries

但我不完全确定您想在这里解决哪个问题。

旁白

• 永远不要引用语言名称。 plpgsql 是一个标识符。 Quoting can lead to unexpected problems .

• 一个只从表中选择的函数可以是declared STABLE .嵌套时重复调用可能有益。

• 一般来说，我会建议升级到当前版本的 Postgres。 8.4 is rather old and will be lose support in less than a year .自 8.4 以来，这方面有了很多改进。