在PostgreSQL中使用选择的值作为表名

Question

我已经搜索了答案，但没有找到。

因此，我有一张表 types

CREATE TABLE types
(
  type_id serial NOT NULL,
  type_name character varying,
  CONSTRAINT un_type_name UNIQUE (type_name)
)

这段内容涉及IT技术，需要翻译成中文。其中提到的users是对应表格的名称，存储了类型名称。虽然这种设计可能有些丑陋，但它允许用户创建自己的类型。（是否有更好的方法实现呢？）

现在我想执行这样的查询：

select type_name, (select count(*) from ???) from types

如何获取所有类型名称及每种类型对象的数量？

这个可以做到吗？

- k102

Answer 1

您不能直接在SQL中完成此操作

您可以使用PLpgSQL函数和动态SQL

CREATE OR REPLACE FUNCTION tables_count(OUT type_name character varying, OUT rows bigint)
RETURNS SETOF record AS $$
BEGIN
  FOR tables_count.type_name IN SELECT types.type_name FROM types
  LOOP
    EXECUTE 'SELECT COUNT(*) FROM ' || quote_ident(tables_count.type_name) INTO tables_count.rows;
    RETURN NEXT;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM tables_count();

- Pavel Stehule

---

哈，Pavel，你抢了我的风头！没想到会有其他人这么快回答。:) 显然，我喜欢你的解决方案。从我提出的东西可以看出来。两个小问题：声明中缺少一个 OUT，而 type_name 声明为 character varying，这将导致你声明的 OUT 参数类型为 text 引发异常。我都修复了。 - Erwin Brandstetter

谢谢！我知道（或者认为我知道）可以用PL/pgSQL完成它，只是想知道是否有其他方法可以实现。 - k102

Answer 2

我没有足够的信息，但是我怀疑你的设计存在问题。你不应该为每种类型都需要一个额外的表。

尽管如此，在纯SQL中无法完成你想做的事情。不过，可以通过执行动态SQL的plpgsql函数来完成。

CREATE OR REPLACE FUNCTION f_type_ct()
  RETURNS TABLE (type_name text, ct bigint) AS
$BODY$
DECLARE
    tbl     text;
BEGIN
    FOR tbl IN SELECT t.type_name FROM types t ORDER BY t.type_name
    LOOP
        RETURN QUERY EXECUTE
        'SELECT $1, count(*) FROM ' || tbl::regclass
        USING tbl;
    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql;

电话：

SELECT * FROM f_type_ct();

你需要学习手册中关于plpgsql的章节才能理解这里发生了什么。

一个特别的提示：将类型转换为regclass是防止SQL注入攻击的保护措施。你也可以使用更普遍适用的quote_ident()，但它不能正确处理模式限定的表名，而类型转换为regclass可以。它还只接受对调用用户可见的表名。

- Erwin Brandstetter

---

太好了！我明白这个函数在做什么 :) 我以为我可以不用函数来完成这个任务。出于性能考虑，每种类型都需要一个额外的表格 - 小表格可以更有效地进行索引，据我所知。 - k102

@k102：顺便说一句，索引在处理大型（或巨大）表格时效果非常好。这也是它们的设计初衷。对于小型表格，顺序读取通常比使用索引更快。 - Erwin Brandstetter