postgresql - 按日期的动态表名称

Question

所以我有需要每晚生成的表格。例如，我有 foo_01jan16、foo_02jan2016、foo_03jan2016 等表。此外，我在每天运行的其他查询中引用这些表。但是，查找和替换似乎效率低下。我想做的是使这个过程自动化。我想做类似的事情:

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

然后在查询中我可以引用table_date()？即

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

    SELECT * FROM table_date() LIMIT 10;

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

谢谢

Answer 1

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

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

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();。

对于查询，您需要使用 CURSOR 为每个查询创建一个函数。按照今天的标准，这是相当低效的，但 PG 8.2 不支持 RETURN NEXT QUERY，这将通过单个语句解决以下功能。所以，困难的方法:

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;

然后你可以像这样调用查询:

SELECT * FROM someday_query1(CURRENT_DATE);

或

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