当动态SQL处于循环中时处理结果 [英] Handle result when dynamic SQL is in a loop

问题描述

我有一堆表，其中有一个"stat"列(stat表示状态；-)

I have a bunch of table that have a "stat" column (stat for status ;-)

我想统计每个统计信息，看看吧！

I would like the count of each stats, and see it!

我的桌子看起来像这样

create table a (
   a_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
   a_stat status_t
);
create table b (
   b_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
   b_stat status_t
);

status_t是一个枚举.

所以我做到了:

DO $$
DECLARE
  tableName RECORD;
  result RECORD;
BEGIN
  SET SEARCH_PATH = projet, public;

  FOR tableName IN SELECT
             c.relname,
             a.attname
           FROM pg_class AS c
             INNER JOIN pg_attribute AS a ON a.attrelid = c.oid
           WHERE a.attname LIKE '%stat' AND c.relkind = 'r' LOOP

      EXECUTE format('SELECT %I, count(%I) FROM %I GROUP BY %I',
                     tableName.attname, tableName.attname, tableName.relname, tableName.attname) INTO result;
      SELECT * FROM result;
  END LOOP;
END;
$$;

有些事情我觉得我在这里做得不好.

There are things that I think I'm not doing well here.

• 也许有更好的格式
• 我无法选择记录，我认为数据类型不好(但无法弄清楚应该使用哪种类型)
• 在for循环中进行选择不是一个好主意(我认为吗?)，但是我没有找到如何将result放入结果数组中并在for循环之后显示它的方法.

• There is maybe a better form of format
• I can't select a RECORD, I think the datatype is not good (but can't figure out what type I should use)
• A select inside a for loop is not a good idea (I think ?) But I didn't found How to put result into a array of result, and display it after the for loop.

如何正确执行此操作?

推荐答案

您不能从DO命令返回.您可以提出通知或写一个临时表来解决此问题.而是改为使用适当的功能.像这样:

You cannot return from a DO command. You could raise notices or write to a temporary table to work around this. But rather use a proper function instead. Like this:

CREATE OR REPLACE FUNCTION foo()
  RETURNS TABLE (sch_name text, tbl_name text, col_name text, row_count_notnull int8) AS
$func$
DECLARE
   _sch text;
   _tbl text;
   _col text;
BEGIN
  FOR _sch, _tbl, _col IN
      SELECT c.relnamespace::regnamespace, c.relname, a.attname
      FROM   pg_class     c
      JOIN   pg_attribute a ON a.attrelid = c.oid
      WHERE  c.relnamespace = ANY ('{projet, public}'::regnamespace[])  -- project?
      AND    c.relkind = 'r'           -- only regular tables
      AND    a.attname LIKE '%\_stat'  -- a_stat, b_stat
      AND    a.attnum > 0              -- only user columns (redundant here)
      AND    NOT a.attisdropped        -- exclude dropped columns
   LOOP
      RETURN QUERY EXECUTE format(
         'SELECT $1, $2, $3, count(%I) FROM %I.%I GROUP BY 1'
       , _col, _sch, _tbl)
      USING _sch, _tbl, _col;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

致电:

SELECT * FROM foo();

要点:

• 在DO命令或plpgsql函数中，都不能没有目标( SELECT * FROM result; )的SELECT. (您可以使用普通的SQL函数，但那里没有循环.)我用RETURN QUERY EXECUTE返回结果.

• You cannot not SELECT without target (SELECT * FROM result;), neither in a DO command nor in a plpgsql function. (You could in a plain SQL function, but you have no loop there.) I return results with RETURN QUERY EXECUTE.

使用USING子句将值传递给EXECUTE.

Schema限定表.否则，您可能会偶然查询错误的表.

Schema-qualify tables in the dynamic query. Else, you might query the wrong table by accident.

不像您的尝试那样包含临时模式(即使您不知道它).您可以根据需要使用pg_my_temp_schema()进行添加，但是您可能仍然不希望添加它:

Does not include the temporary schema like your attempt (even if you were not aware of it). You could add it using pg_my_temp_schema() if you want, but you probably did not want it anyway:

• 如何search_path是否会影响标识符解析度和当前模式"
• 每个连接的临时模式?

• How does the search_path influence identifier resolution and the "current schema"
• Temporary schema per connection?

相关:

• 表名作为PostgreSQL函数参数

在SO上的plpgsql中搜索有关动态SQL的更多示例.

这篇关于当动态SQL处于循环中时处理结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！