代码之家 › 专栏 › 技术社区 › Bizmark

从另一个表中的表名列表填充临时表

union-all dynamic-sql plpgsql postgresql sql

Bizmark · 技术社区 · 10 年前

我一直在其他线程上寻找解决这个问题的方法,但是我从其他主题中得到了一些我需要的东西,这些东西我无法结合在一起正确工作。

问题的背景是:每晚都会将事务日志写入数据库中新创建的表,然后清除主日志。创建的表的名称与日期无关(这使得搜索历史日志变得困难——因此无法猜测或硬编码给定日期的表名称)。

但是,一旦事务被移动到新表中,日志记录表就会添加一条新记录,该记录存储了日期和有关存档记录的其他信息(包括表的名称)。

我正在尝试编写一个函数,该函数允许我将日期范围(开始日期和结束日期)传递到其中,然后返回在给定日期范围内具有信息的所有表的名称-将名称存储在数组中。填充阵列后,将对阵列中的每个表运行SQL查询,以将所有这些写入单个临时表,从而允许对其进行查询。

一些伪代码可能是:

CREATE OR REPLACE FUNCTION transactions(begin_date varchar(10), end_date varchar(10)) RETURN void as $$
Begin
SELECT INTO table_names 'name' from 'log_table' where 'log_date' between begin_date and end_date;
FOREACH table_names LOOP
SQL_Query = "INSERT INTO temporary_table SELECT * from " + table_names;
EXECUTE SQL_Query;
END LOOP;

任何帮助都很感谢-我的逻辑可能会在这一点上完全崩溃,我需要改变我的方法。

1 回复 | 直到 10 年前

Community Dai 7 年前

CREATE OR REPLACE FUNCTION f_create_temp_trans(_begin date
                                              ,_end   date, OUT _rows int) AS
$func$
BEGIN

DROP TABLE IF EXISTS temp_trans;
CREATE TEMP TABLE IF NOT EXISTS temp_trans (LIKE existing_tbl);

EXECUTE (
   SELECT E'INSERT INTO temp_trans\NSELECT * FROM '
       || string_agg(quote_ident(tbl_name)
                   , E'\NUNION ALL SELECT * FROM '
                     ORDER  BY log_date)
   FROM   log_table
   WHERE  log_date BETWEEN _begin AND _end
   );

GET DIAGNOSTICS _rows = ROW_COUNT;

END
$func$ LANGUAGE plpgsql;

电话:

SELECT * FROM f_create_temp_trans('2014-06-01', '2014-06-11');

返回插入临时表的行数。

主要要点

创建名为 temp_trans (在会话期间有效)。结构是复制形式 existing_tbl -替换为实际的表名。
使用聚合函数 string_agg() 编写 仅有一个的 INSERT 具有 UNION ALL ,这比运行许多 插入,插入 声明。

生成并执行以下形式的语句:

INSERT INTO temp_trans
SELECT * FROM tbl1
UNION ALL SELECT * FROM "trickY name tbl2"
UNION ALL SELECT * FROM tbl3
...

单引号( 'val' )用于值,而不是标识符,如果需要,可以使用双引号。
为什么要约会 varchar(10) ? 使用 date 我的代码应该是这样的。
关于 GET DIAGNOSTICS .
E'\N' 是换行符。只是为了修饰生成的SQL代码以进行调试。不需要。您可以用空格替换:
```
...
SELECT 'INSERT INTO temp_trans SELECT * FROM '
    || string_agg(quote_ident(tbl_name), ' UNION ALL SELECT * FROM ' ...
```
关于引号和转义字符串:
- Insert text with single quotes in PostgreSQL