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

PG 8.2 is older than the iPhone! There has been no support for it (such as bug and security fixes) in 4.5 years. Unless you built it yourself in the past year it therefore has "nice features" such as the [Heartbleed](http://heartbleed.com) vulnerability. Upgrade! — Patrick, Jun 10 '16 at 16:12
Possible duplicate of [variable reference for postgreSQL](http://stackoverflow.com/questions/37738042/variable-reference-for-postgresql) — Nick, Jun 10 '16 at 21:42
@Nicarus That question does not have an accepted answer so wouldn't qualify as a duplicate — Patrick, Jun 11 '16 at 06:26

Patrick · Answer 1 · 2016-06-11T06:24:06.517

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

So I tried this with a small toy table, and I keep returning an error: `RETURN NEXT must specify a record or row variable in function...` There seems to be a problem with the `RETURN NEXT 'SELECT ... ` — windsormatic, Jun 10 '16 at 17:40
That is not obvious from the documentation, but ok. See alternative approach above. If this doesn't work then you are out of luck. — Patrick, Jun 11 '16 at 06:24

Dynamic table names by date

1 Answers1

Linked