动态 SQL 处于循环状态时的处理结果

Question

我有一堆表格，其中有一个"stat"列(stat for status ;-)

我想要每个统计数据的计数，看看它！

我的表格看起来像这样

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;
$$;

有些事情我认为我在这里做得不好。

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

如何正确地做到这一点？

Answer 1

不能从DO命令返回。您可以发出通知或写信给临时表来解决此问题。而是使用适当的函数。喜欢这个：

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();

要点：

• 你不能SELECT没有目标(SELECT * FROM result;)，无论是在DO命令中还是在plpgsql函数中。(你可以在一个普通的SQL函数中，但你在那里没有循环。我返回带有RETURN QUERY EXECUTE的结果。

• 使用USING子句将值传递给EXECUTE。

• 动态查询中的架构限定表。否则，您可能会意外查询错误的表。

• 不包含像您的尝试那样的临时架构(即使您不知道它)。如果需要，您可以使用pg_my_temp_schema()添加它，但您可能无论如何都不想要它：

  • search_path如何影响标识符解析和"当前架构">
  • 每个连接的临时架构？

相关：

• 作为 PostgreSQL 函数参数的表名

在 SO 上搜索更多在 plpgsql 中使用动态 SQL 的示例。