如何在 PL/pgSQL 中按行类型返回表 [英] How to return a table by rowtype in PL/pgSQL

问题描述

我正在尝试使用 PL/pgSQL (PostgreSQL 9.3) 实现一个函数，该函数返回一个与参数中的输入表具有相同结构的表.基本上，我想更新一个表，并用 plpgsql 返回更新后的表的副本.我搜索了 SO，发现了几个相关的问题(例如 Return dynamic table with unknown columns from PL/pgSQL function 和 表名作为 PostgreSQL 函数参数)，这导致以下最小测试示例:

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  -- problem solved
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
      'UPDATE %s SET val = 2 RETURNING *;'
     , pg_typeof(_tbl_type))
     );
END
$func$;

调用(重要):

SELECT * FROM change_val(NULL::some_tbl);

db<>小提琴这里
_{旧版sqlfiddle}

见(最后一段):

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

这篇关于如何在 PL/pgSQL 中按行类型返回表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！