遍历架构中的类似表 [英] Loop through like tables in a schema

问题描述

Postgres 9.1-我有一个模式，该模式具有按月对表进行分区的表(每月创建一个新表，所有列均相同).没有使用主"表将其设置为普通分区.我目前正在编写一个相当大的查询，每个月我将不得不运行几次.

Postgres 9.1 - I have a schema that has tables "partitioned" by month (a new table is created each month, all columns the same). It is not set up as normal partitioning with a "master" table. I am currently writing a fairly large query, that I will have to run a few times each month.

模式:augmented_events
表格:
p201301(2013年1月)
p201302(2013年2月)
p201303(2013年3月)
...
p201312(2013年12月)
p201401(2014年1月)

Schema: augmented_events
tables:
p201301 (January 2013)
p201302 (Feb 2013)
p201303 (March 2013)
...
p201312 (December 2013)
p201401 (January 2014)

现在，我必须将我的(简化的)查询写为:

Right now I have to write my (simplified) query as:

select *    
from augmented_events.p201301  
union   
select *      
from augmented_events.p201302  
union  
select *      
from augmented_events.p201303  
union  
select *      
from augmented_events.p201312  
union  
select *      
from augmented_events.p201401 

每个月我都需要添加新的月份.我想使它更具可扩展性，而不必每月重新进行审查.我是否可以创建一个函数(或存在的函数)来循环遍历augmented_events模式中的每个表，并将其视为要合并这些表一样对待?

And every month I need to add in the new month. I would like to make this a little more scalable without me having to revisit it every month. Is there a function I can create (or one that exists) that loops through each table in the augmented_events schema, and treats it as if I was to union these tables?

推荐答案

正确的解决方案

...将通过 INHERITANCE .实际上，这很简单.考虑以下相关答案:
选择(检索)来自多个的所有记录使用Postgres的模式

Proper solution

... would be partitioning via INHERITANCE. It's rather simple actually. Consider this related answer:
Select (retrieve) all records from multiple schemas using Postgres

在不幸的设计中，您可以通过EXECUTE在plpgsql函数中使用动态SQL.

While stuck with your unfortunate design, you can use dynamic SQL in a plpgsql function with EXECUTE.

创建此函数一次:

CREATE OR REPLACE FUNCTION f_all_in_schema_foo()
  RETURNS SETOF t AS
$func$
BEGIN
RETURN QUERY EXECUTE (
   SELECT string_agg(format('SELECT * FROM %s', c.oid ::regclass)
                    ,E'\nUNION ALL\n'
                     ORDER BY relname)
   FROM   pg_namespace n
   JOIN   pg_class c ON c.relnamespace = n.oid
   WHERE  n.nspname = 'foo'
   AND    c.relkind = 'r'
   );
END
$func$  LANGUAGE plpgsql;

请注意我如何谨慎地避免进行 SQL注入(表名称必须视为用户输入"！).请考虑以下相关答案中的详细建议:
表名作为PostgreSQL函数参数
生成并执行以下形式的查询:

Note how I carefully avoid any possibility for SQL injection (table names have to be considered as "user input"!). Consider detailed advice in this related answer:
Table name as a PostgreSQL function parameter
Generates and executes a query of the form:

SELECT * FROM foo.p201301 
UNION ALL
SELECT * FROM foo.p201302 
UNION ALL
SELECT * FROM foo.p201303 
UNION ALL
...

由于string_agg()中的ORDER BY子句，因此按名称对表进行排序.

Tables are ordered by name due to the ORDER BY clause in string_agg().

您可以像使用表格一样使用此表格功能.致电:

You can use this table function just like a table. Call:

SELECT * FROM f_all_in_schema_foo();

性能应该很好.

您可以在SO 此处找到具有说明和链接的类似示例.搜索.

这篇关于遍历架构中的类似表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！