Outside vendor created the database this way. Not sure why, exactly. However, in the interim, I have determined a work around. All the daily tables inherit their columns from a parent table. In this example the parent base table is "tnt_audit.summaryreport_login_stats" and when I query it I receive data from all child tables. Now I can run the following query:<div>
<br></div><div><div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>count(*) AS </div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>tnt_audit.summaryreport_login_stats,</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>tnt_config.node</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>tnt_audit.summaryreport_login_stats.first_record = 't' AND</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>tnt_audit.summaryreport_login_stats.login_time >= '2011-04-12 00:00:00' AND</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>tnt_audit.summaryreport_login_stats.node_id = <a href="http://tnt_config.node.id">tnt_config.node.id</a> AND</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>LOWER(tnt_config.node.fqmn) LIKE 'uswsve%'</div><div>;</div></div><div><br></div><div>Which will yield results from the specified date. I should be able to use a dynamic date in the WHERE clause.</div>
<div><br></div><div>Thanks,</div><div><br clear="all"><b>Richard Maloley II</b><div><i>Rick and Richard Computer Consulting</i></div><div>p: 616-745-6914</div><div>e: <a href="mailto:richard@rrcomputerconsulting.com" target="_blank">richard@rrcomputerconsulting.com</a></div>
<div>w: <a href="http://www.rrcomputerconsulting.com" target="_blank">http://www.rrcomputerconsulting.com</a></div><div><br></div><br>
<br><br><div class="gmail_quote">On Tue, Apr 12, 2011 at 2:35 PM, David Hoppe <span dir="ltr"><<a href="mailto:dave@hopasaurus.com">dave@hopasaurus.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div bgcolor="#ffffff" text="#000000">
Is there a really good reason for having each day in a separate
table? If not put all the data in a single table with a field for
the date and your query turns into "select * form
tnt_audi.summaryreport_login_stats where datefield*=(CURRENT_DATE)"
<br>
<br>
*datefield is whatever the date field becomes.<br>
<br>
If there is a really good reason I would be happy to try harder but
would like to know that there really is a reason.<br><font color="#888888">
<br>
<br>
<br>
David Hoppe</font><div><div></div><div class="h5"><br>
<br>
<br>
<br>
On 4/12/2011 2:10 PM, Richard Maloley II wrote:
</div></div><blockquote type="cite"><div><div></div><div class="h5">Hello,
<div><br>
</div>
<div>I'm trying to create a really simple query against a
Postgresql database. This database creates a new table every
single day for daily statistics, therefore all tables are named
the same except for the different date appended to the name.</div>
<div><br>
</div>
<div>My query right now is simple: Execute a single SQL statement
that will gather data from the correct daily table depending on
the day. For example:</div>
<div><br>
</div>
<div>SELECT * FROM tnt_audit.summaryreport_login_stats_20110412; </div>
<div><br>
</div>
<div>The above query will select all of the data for today. The
date piece, 20110412, needs to be dynamically changed. Here is
what I thought of:</div>
<div><br>
</div>
<div>
<div>SELECT</div>
<div><span style="white-space:pre-wrap"> </span>*</div>
<div>FROM</div>
<div><span style="white-space:pre-wrap"> </span>tnt_audit.summaryreport_login_stats_(CURRENT_DATE)</div>
<div>;</div>
</div>
<div><br>
</div>
<div>However this results in the following error message:</div>
<div><br>
</div>
<div>
<div>ERROR: function tnt_audit.summaryreport_login_stats_(date)
does not exist</div>
<div>LINE 4: tnt_audit.summaryreport_login_stats_(CURRENT_DATE)</div>
<div> ^</div>
<div>HINT: No function matches the given name and argument
types. You might need to add explicit type casts.</div>
<div><br>
</div>
<div>********** Error **********</div>
<div><br>
</div>
<div>ERROR: function tnt_audit.summaryreport_login_stats_(date)
does not exist</div>
<div>SQL state: 42883</div>
<div>Hint: No function matches the given name and argument
types. You might need to add explicit type casts.</div>
<div>Character: 17</div>
</div>
<div><br>
</div>
<div>I'm no SQL expert and I've been unable to find any direction
on how to accomplish this. I feel as though it should be simple
to append this data to the table name but the answer eludes me.
For reference we are using version 8.x of the database.</div>
<div><br>
</div>
<div>Thanks,</div>
<div><b>Richard Maloley II</b>
<div><i>Rick and Richard Computer Consulting</i></div>
<div>p: <a href="tel:616-745-6914" value="+16167456914" target="_blank">616-745-6914</a></div>
<div>e: <a href="mailto:richard@rrcomputerconsulting.com" target="_blank">richard@rrcomputerconsulting.com</a></div>
<div>w: <a href="http://www.rrcomputerconsulting.com" target="_blank">http://www.rrcomputerconsulting.com</a></div>
<div><br>
</div>
<br>
</div>
<br></div></div>
-- <br><div class="im">
This message has been scanned for viruses and
<br>
dangerous content by
<a href="http://www.mailscanner.info/" target="_blank"><b>MailScanner</b></a>,
and is
<br>
believed to be clean.
<pre><fieldset></fieldset>
_______________________________________________
grlug mailing list
<a href="mailto:grlug@grlug.org" target="_blank">grlug@grlug.org</a>
<a href="http://shinobu.grlug.org/cgi-bin/mailman/listinfo/grlug" target="_blank">http://shinobu.grlug.org/cgi-bin/mailman/listinfo/grlug</a></pre>
</div></blockquote><div class="im">
<br>
<br>--
<br>This message has been scanned for viruses and
<br>dangerous content by
<a href="http://www.mailscanner.info/" target="_blank"><b>MailScanner</b></a>, and is
<br>believed to be clean.
</div></div>
<br>_______________________________________________<br>
grlug mailing list<br>
<a href="mailto:grlug@grlug.org">grlug@grlug.org</a><br>
<a href="http://shinobu.grlug.org/cgi-bin/mailman/listinfo/grlug" target="_blank">http://shinobu.grlug.org/cgi-bin/mailman/listinfo/grlug</a><br></blockquote></div><br></div>
<br />--
<br />This message has been scanned for viruses and
<br />dangerous content by
<a href="http://www.mailscanner.info/"><b>MailScanner</b></a>, and is
<br />believed to be clean.