By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,118 Members | 1,134 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,118 IT Pros & Developers. It's quick & easy.

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

P: 2
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.

Expand|Select|Wrap|Line Numbers
  1. Select * 
  2. from log_file_07_01_2013
  3. union
  4. Select * 
  5. from log_file_07_02_2013
  6. ...
  7. Union
  8. Select * 
  9. from log_file_07_07_2013
Jul 25 '13 #1

✓ answered by 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

Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,364
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
Jul 25 '13 #2

P: 2
The database is externally managed so I cannot change the schema as much as I would like to.
Jul 25 '13 #3

Rabbit
Expert Mod 10K+
P: 12,364
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
Jul 25 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.