[GRLUG] Postgresql help

[Richard Maloley II]

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:

SELECT
count(*) AS
FROM
tnt_audit.summaryreport_login_stats,
 tnt_config.node
WHERE
tnt_audit.summaryreport_login_stats.first_record = 't' AND
 tnt_audit.summaryreport_login_stats.login_time >= '2011-04-12 00:00:00' AND
tnt_audit.summaryreport_login_stats.node_id = tnt_config.node.id AND
 LOWER(tnt_config.node.fqmn) LIKE 'uswsve%'
;

Which will yield results from the specified date. I should be able to use a
dynamic date in the WHERE clause.

Thanks,

*Richard Maloley II*
*Rick and Richard Computer Consulting*
p: 616-745-6914
e: richard at rrcomputerconsulting.com
w: http://www.rrcomputerconsulting.com




On Tue, Apr 12, 2011 at 2:35 PM, David Hoppe <dave at hopasaurus.com> wrote:

>  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)"
>
> *datefield is whatever the date field becomes.
>
> 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.
>
>
>
> David Hoppe
>
>
>
>
> On 4/12/2011 2:10 PM, Richard Maloley II wrote:
>
> Hello,
>
>  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.
>
>  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:
>
>  SELECT * FROM tnt_audit.summaryreport_login_stats_20110412;
>
>  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:
>
>  SELECT
>  *
> FROM
>  tnt_audit.summaryreport_login_stats_(CURRENT_DATE)
> ;
>
>  However this results in the following error message:
>
>  ERROR:  function tnt_audit.summaryreport_login_stats_(date) does not
> exist
> LINE 4:  tnt_audit.summaryreport_login_stats_(CURRENT_DATE)
>          ^
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
>
>  ********** Error **********
>
>  ERROR: function tnt_audit.summaryreport_login_stats_(date) does not exist
> SQL state: 42883
> Hint: No function matches the given name and argument types. You might need
> to add explicit type casts.
> Character: 17
>
>  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.
>
>  Thanks,
> *Richard Maloley II*
> *Rick and Richard Computer Consulting*
> p: 616-745-6914
> e: richard at rrcomputerconsulting.com
> w: http://www.rrcomputerconsulting.com
>
>
>
> --
> This message has been scanned for viruses and
> dangerous content by *MailScanner* <http://www.mailscanner.info/>, and is
> believed to be clean.
>
>
> _______________________________________________
> grlug mailing listgrlug at grlug.orghttp://shinobu.grlug.org/cgi-bin/mailman/listinfo/grlug
>
>
>
> --
> This message has been scanned for viruses and
> dangerous content by *MailScanner* <http://www.mailscanner.info/>, and is
> believed to be clean.
>
> _______________________________________________
> grlug mailing list
> grlug at grlug.org
> http://shinobu.grlug.org/cgi-bin/mailman/listinfo/grlug
>

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://shinobu.grlug.org/pipermail/grlug/attachments/20110412/724975b8/attachment-0001.html>