Dynamic table names by date

Question

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;

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;

Something like that. I am using postgreSQL 8.2.

Thanks

Answer 1

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;

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

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

or

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