PostgreSQL函数从动态表名称返回结果集

3okqufwl 于 2023-04-29 发布在 PostgreSQL

关注(0)|答案(1)|浏览(200)

在我的数据库中，我有标准的应用程序表和备份表。例如，对于表“employee”，我有一个名为“巴克_employee”的表。巴克_employee表是employee表的备份。我用它来在测试之间还原employee表。
我想我可以使用这些“巴克_”表来查看在测试期间发生的更改，如下所示：

SELECT * FROM employee EXCEPT SELECT * FROM bak_employee

这将显示插入和更新的记录。我暂时忽略已删除的记录。
现在，我想做的是遍历数据库中的所有表，看看是否有任何表中的任何更改。我想把它作为一个函数来做，这样就很容易一遍又一遍地调用。这是我目前掌握的情况：

CREATE OR REPLACE FUNCTION public.show_diff()
  RETURNS SETOF diff_tables AS
$BODY$

DECLARE
  app_tables text;
BEGIN

    FOR app_tables IN
        SELECT table_name  

        FROM   information_schema.tables 

        WHERE  table_catalog = 'myDatabase' 
          AND  table_schema = 'public'
          AND  table_name not like 'bak_%'          -- exclude existing backup tables
    LOOP

        -- somehow loop through tables to see what's changed something like:
        EXECUTE 'SELECT * FROM ' || app_tables || ' EXCEPT SELECT * FROM bak_' || app_tables;

    END LOOP;

    RETURN;
END;
$BODY$
LANGUAGE plpgsql;

但显然这不会给我任何有用的信息。任何帮助将不胜感激。

postgresql

来源：https://stackoverflow.com/questions/37550856/postgresql-function-returning-result-set-from-dynamic-tables-names

1条答案

按热度按时间

rsl1atfo1#

不能在同一调用中从同一函数返回不同的已知行类型。一个便宜的修复方法是将每个行类型强制转换为text，这样我们就有了一个通用的返回类型：

CREATE OR REPLACE FUNCTION public.show_diff()
  RETURNS SETOF text
  LANGUAGE plpgsql AS   -- text!!
$func$
DECLARE
   app_table text;
BEGIN
   FOR app_table IN
      SELECT table_name  
      FROM   information_schema.tables 
      WHERE  table_catalog = 'myDatabase' 
      AND    table_schema = 'public'
      AND    table_name NOT LIKE 'bak_%'   -- exclude existing backup tables
   LOOP
      RETURN NEXT ' ';
      RETURN NEXT '=== ' || app_table || ' ===';
      RETURN QUERY EXECUTE format(
        'SELECT x::text FROM (TABLE %I EXCEPT ALL TABLE %I) x'
       , app_table, 'bak_' || app_table);
   END LOOP;

   RETURN;
END
$func$;

电话：

SELECT * FROM public.show_diff();

一开始我有@a_horse建议的测试，但在你的评论之后，我意识到没有必要这样做。EXCEPT认为NULL的值 * 相等 *，并显示 * 所有 * 差异。
同时，我改进并简化了您的解决方案。使用EXCEPT ALL：更便宜并且不会冒折叠完整副本的风险。

在PostgreSQL中使用EXCEPT子句

TABLE只是语法糖。参见：

有SELECT * FROM的快捷方式吗？

然而，如果你有一个唯一列（组合）的索引，像我之前建议的JOIN应该更快：通过索引找到唯一可能的副本应该相当便宜。
关键元素是将行类型转换为text（x::text）。
你甚至可以让这个函数对任何表都起作用，但一次不能超过一个：使用多态参数类型：

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

赞(0）回复(0）举报 2023-04-29

我来回答

PostgreSQL函数从动态表名称返回结果集

1条答案

相关问题

热门标签

最新问答