Populate temporary table from a list of table names in another table

Question

I've been looking for a solution to this on other threads however I'm getting just bits and pieces of what I need from other topics which I can't tie together to work correctly.

The background of the problem is this: Each night a transaction log is written to a newly created table in the database, then the primary log is cleared. The name of the table created isn't related to the date (making searching for historical logs difficult - so the table name for a given date can't be guessed or hard coded).

However once the transactions are moved into the new table, a logging table has a new record added to it which stores the dates and other information about the archived records (including the name of the table).

I'm trying to write a function which allows me to pass a date range (start date and end date) into it, then return the name of all tables which have information within the given date range - storing the names in an array. Once the array is populated a SQL query is run against each table in the array to write all of this to a single temporary table, allowing it to be queried.

Some pseudo code would be:

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;

Any help is appreciated - my logic could be entirely broken on this and I'll need to change my methodology.

Answer 1

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;

Call:

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

Returns the number of rows inserted into the temp table.

Major points

• Creates a temp table (pre-emptive) named temp_trans (lives for the duration of the session). Structure is copied form existing_tbl - replace with an actual table name.

• Using the aggregate function string_agg() to compose a single INSERT with UNION ALL, which is more efficient than running many INSERT statements.

• Produces and executes a statement of the form:

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

• Single quotes ('val') are for values, not for identifiers, where you use double-quoted if necessary.

• Why are your dates varchar(10)? Using date in my code like it should be.

• About GET DIAGNOSTICS.

• E'\N' is a line break. Just to prettify the generated SQL code for debugging. Not needed. You can replace with blanks:

  ...
  SELECT 'INSERT INTO temp_trans SELECT * FROM '
      || string_agg(quote_ident(tbl_name), ' UNION ALL SELECT * FROM ' ...
  

  About quoting and escape strings:

  • Insert text with single quotes in PostgreSQL