PostgreSQL - 如何在具有相同模式的多个表上运行查询

Question

PostgreSQL - 如何在具有相同模式的多个表上运行查询

sqlpostgresqlstored-proceduresdynamic-sql

9

我有一个Postgres数据库，其中有数百个表。数据库中的子集Foo 具有相同的架构。

理想情况下，我希望创建一个存储过程，可以针对单个表或 subset Foo 中的所有表运行查询。

伪代码:

CREATE TABLE tbl_a (id INTEGER, name VARCHAR(32), weight double, age INTEGER);
CREATE TABLE tbl_b (id INTEGER, name VARCHAR(32), weight double, age INTEGER);
CREATE TABLE tbl_c (id INTEGER, name VARCHAR(32), weight double, age INTEGER);
CREATE TABLE tbl_d (id INTEGER, name VARCHAR(32), weight double, age INTEGER);

CREATE TYPE person_info AS (id INTEGER, name VARCHAR(32), weight double, age INTEGER);

CREATE FUNCTION generic_func(ARRAY one_or_more_table_names)
    RETURNS person_info 
    -- Run query on table or all specified tables
    AS $$  $$
    LANGUAGE SQL;

我该如何在Postgresql 9.x中实现这个需求？

- Homunculus Reticulli

1

不相关，但是：“Postgres 9.x”涵盖了7个不同的主要版本（其中两个已不再受支持）。您需要更具体地指定Postgres版本。 - user330315

2个回答

6

要动态创建使用数组中的项（表名）的选择查询，您可以使用以下选择语句

SELECT string_agg(q, ' union all ')
FROM  (
    SELECT 'select * from ' || unnest(array ['tble_a','tble_b']) AS q
    ) t

结果：

string_agg                                          
--------------------------------------------------- 
select * from tble_a union all select * from tble_b

您可以创建一个返回带列的表格的函数。

 id INTEGER
,name VARCHAR(32)
,weight numeric
,age INTEGER

P.S：我避免使用TYPE person_info 功能：

CREATE
    OR REPLACE FUNCTION generic_func (tbl varchar [])
RETURNS TABLE (         -- To store the output
        id INTEGER
        ,name VARCHAR(32)
        ,weight numeric
        ,age INTEGER
        ) AS $BODY$

DECLARE qry text;

BEGIN
    SELECT string_agg(q, ' union all ')  --To create select query dynamically
    INTO qry
    FROM (
        SELECT 'select * from ' || unnest(tbl) AS q
        ) t;

    RAISE NOTICE 'qry %',qry; --optional

    RETURN query --Executes the query to the defined table

    EXECUTE qry;
END;$BODY$

LANGUAGE plpgsql VOLATILE

使用方法：

select * from generic_func(array['tbl_a','tbl_b','tbl_c','tbl_d'])

结果：

id name weight age 
-- ---- ------ --- 
2  ABC  11     112 
2  CBC  11     112 
2  BBC  11     112 
2  DBC  11     112

并且

选择 * 从一般函数（数组['tbl_a']）

Result:
id name weight age 
-- ---- ------ --- 
2  ABC  11     112

- Vivek S.

+1 我喜欢这个方法，因为它允许我指定表的名称。然而，我后来发现这被认为是反模式（https://dev59.com/z2gv5IYBdhLWcg3wSe-2）。因此，我目前更倾向于Fabian的答案——它相当优雅地满足了“多表查询”的要求——但似乎不太灵活（需要每个表都有自己的查询版本，其中表名在查询/SP中硬编码）。 - Homunculus Reticulli

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Fabian Pijcke · Accepted Answer

你应该查看PostgreSQL中的表继承，它们可以完全实现你所说的内容。

例如，你可以创建一个名为parent_tbl的表：

CREATE TABLE parent_tbl (id INTEGER, name VARCHAR(32), weight numeric, age INTEGER);

然后将您的表格链接到此父表：

ALTER TABLE tbl_a INHERIT parent_tbl;
ALTER TABLE tbl_b INHERIT parent_tbl;
ALTER TABLE tbl_c INHERIT parent_tbl;
ALTER TABLE tbl_d INHERIT parent_tbl;

那么对于 parent_tbl 的 SELECT 查询将会查询 tbl_x 表的所有数据，而对于 tbl_x 的查询则仅查询该表。

INSERT INTO tbl_a VALUES (1, 'coucou', 42, 42);

SELECT * FROM tbl_a;
 id |  name  | weight | age 
----+--------+--------+-----
  1 | coucou |     42 |  42
(1 row)

SELECT * FROM parent_tbl;
 id |  name  | weight | age 
----+--------+--------+-----
  1 | coucou |     42 |  42
(1 row)

SELECT * FROM tbl_b;
 id |  name  | weight | age 
----+--------+--------+-----
(0 rows)

您可以从给定的子表中筛选数据。例如，如果您对来自tbl_a和tbl_b表的数据感兴趣，可以执行以下操作：

select id, name, weight, age
from parent_tbl
left join pg_class on oid = parent_tbl.tableoid
where relname in ('tbl_a', 'tbl_b');

编辑：我将重量的类型更改为数字，因为我的服务器不支持double类型。