如何在PL/pgSQL中按行类型返回表

问题描述：

我正在尝试使用PL/pgSQL(PostgreSQL 9.3)实现一个函数，该函数返回一个表，该表的结构与参数中的输入表相同.基本上，我想更新一个表，并使用plpgsql返回更新后的表的副本.我在SO周围搜索，发现了一些相关的问题(例如从PL/pgSQL函数中返回带有未知列的动态表和

I am trying to implement a function that returns a table with the same structure as an input table in the parameter, using PL/pgSQL (PostgreSQL 9.3). Basically, I want to update a table, and return a copy of the updated table with plpgsql. I searched around SO and found several related questions (e.g. Return dynamic table with unknown columns from PL/pgSQL function and Table name as a PostgreSQL function parameter), which lead to the following minimal test example:

CREATE OR REPLACE FUNCTION change_val(_lookup_tbl regclass)
RETURNS _lookup_tbl%rowtype AS --problem line
$func$
BEGIN
    RETURN QUERY EXECUTE format('UPDATE %s SET val = 2 RETURNING * ; ', _lookup_tbl);
END
$func$ LANGUAGE plpgsql;

但是我不能不给problem line中的TABLE或SETOF RECORD提供正确的返回类型.根据此答案:

But I can't get past giving the correct return type for TABLE or SETOF RECORD in the problem line. According to this answer:

SQL要求在调用时知道返回类型

SQL demands to know the return type at call time

但是我认为返回类型(我打算从输入表类型中借用)是已知的.有人可以帮忙解释一下是否可以修复上述PL/pgSQL函数的签名吗?

But I think the return type (which I intend to borrow from the input table type) is known. Can some one help explain if it is possible to fix the signature of the above PL/pgSQL function?

注意，我需要对输入表进行参数设置并返回该表的更新.欢迎其他选择.

Note, I need to parametrize the input table and return the update of that table. Alternatives are welcome.

答

到目前为止，您所拥有的东西看起来不错.缺少的成分: 多态类型 .

What you have so far looks good. The missing ingredient: polymorphic types.

CREATE OR REPLACE FUNCTION change_val(_tbl_type anyelement)
  RETURNS SETOF anyelement AS -- problem solved
$func$
BEGIN
   RETURN QUERY EXECUTE format(
      'UPDATE %s SET val = 2 RETURNING *;'
     , pg_typeof(_tbl_type))
     );
END
$func$ LANGUAGE plpgsql;

致电(重要):

SELECT * FROM change_val(NULL::some_tbl);

SQL小提琴.

更多详细信息(最后一段):

More details (last paragraph):

重构PL/pgSQL函数以返回各种SELECT查询的输出

如何在PL/pgSQL中按行类型返回表

相关推荐