Creating a dynamic view for a union query based on a rolling date

[David Gerson]

I am trying to create a query with a rolling date range to combine log files from the last week. Each day a new log table is created (log_file_07_01 _2013).

I want to create a view which would dynamically update the query for the last seven days of histoy:

i.e.

Select * 
from log_file_07_01_2013
union
Select * 
from log_file_07_02_2013
...
Union
Select * 
from log_file_07_07_2013

[Rabbit]

Please use code tags when posting code or formatted data.

Your current table structure breaks the rules of normalization. You should not have a different table for each date. Instead you should have just one table with a date field. That way, querying would be a lot easier by using a dynamic date range in the WHERE clause. What you are trying to do now with dynamic table names is very difficult because of the flawed table structure.

You can read more about normalization in our article here: http://bytes.com/topic/access/insigh...ble-structures

[David Gerson]

The database is externally managed so I cannot change the schema as much as I would like to.

[Rabbit]

If you can't change the table structure, then you'll need to create a function that builds and executes a dynamic SQL string. More on that can be found in the PostGreSQL documentation here: http://www.postgresql.org/docs/curre...-EXECUTING-DYN