日期动态表名称 [英] Dynamic table names by date

问题描述

所以我有需要每晚生成的表。作为示例，我有诸如foo_01jan16，foo_02jan2016，foo_03jan2016等的表。此外，我在每天运行的其他查询中引用了这些表。但是，查找和替换似乎效率很低。我想做的就是自动化这个过程。我想执行以下操作：

So I have tables that I need to generate nightly. As an example I have tables such as foo_01jan16, foo_02jan2016, foo_03jan2016, etc. Additionally I reference these table(s) in other queries that I run daily. However, find and replace seems inefficient. What I want to do is automate this process. I want to do something like:

    CREATE OR REPLACE FUNCTION table_date() RETURNS text AS $$
            SELECT 'foo_'||to_char(current_timestamp, 'DDMONYY') AS result
    $ LANGUAGE SQL;

然后在查询中我可以引用 table_date（）？即

Then in query I can reference table_date()? i.e.

    CREATE TABLE table_date() AS
    SELECT * FROM base_table WHERE date <= current_date;

    SELECT * FROM table_date() LIMIT 10;

类似的东西。我正在使用postgreSQL 8.2。

Something like that. I am using postgreSQL 8.2.

谢谢

推荐答案

不，您不能这样做，因为PG需要一个字符串文字作为表名，而不是一些表达式。像往常一样，PG中有一种变通方法，以PL / pgSQL函数中的动态查询的形式。

No, you can't do that because PG needs a string literal for the table name, not some expression. As usual, there is a work-around in PG, in the form of a dynamic query in a PL/pgSQL function.

首先，您必须创建表并填充它：

First you have to create the table and populate it:

CREATE FUNCTION todays_data() RETURNS void AS $$
BEGIN
  EXECUTE 'CREATE TABLE foo_' || to_char(CURRENT_DATE, 'DDMONYYYY') ||
          ' AS SELECT * FROM base_table WHERE date <= CURRENT_DATE';
END;
$$ LANGUAGE plpgsql;

您应该每天调用一次此函数： SELECT todays_data（）; 。

You should call this function once per day: SELECT todays_data();.

对于查询，您需要使用 CURSOR 。按照当今的标准，这是相当低效的，但是PG 8.2不支持返回下一个查询，该问题可以通过一个语句解决以下功能。因此，比较困难的方法是：

For the queries you need to make a function for each of them, using a CURSOR. This is rather inefficient by today's standards, but PG 8.2 does not have support for RETURN NEXT QUERY which would solve the below function with a single statement. So, the hard way:

CREATE FUNCTION someday_query1(dt date) RETURNS SETOF base_table AS $$
DECLARE
  cur refcursor;
  rec base_table%ROWTYPE;
BEGIN
  OPEN cur FOR EXECUTE 'SELECT * FROM foo_' || to_char(dt, 'DDMONYYYY') ||
                       ' WHERE some_condition';
  FETCH cur INTO rec;
  WHILE FOUND LOOP
    RETURN NEXT rec;
    FETCH cur INTO rec;
  END LOOP;
  CLOSE cur;
END;
$$ LANGUAGE plpgsql STRICT;

然后您可以像这样调用查询：

Then you can call the queries like so:

SELECT * FROM someday_query1(CURRENT_DATE);

或

SELECT * FROM someday_query1('2016-01-23');

这篇关于日期动态表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！