[GRLUG] Postgresql help

[Adam Tauno Williams]

On Tue, 2011-04-12 at 14:10 -0400, Richard Maloley II wrote:

> 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)
> ;

> 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.

RETURN QUERY EXECUTE

<http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html>

But is this table-by-day really necessary?  I've never found a
table-size issue that couldn't be dealt with via partitioning
<http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html>
and/or conditional indexes
<http://www.postgresql.org/docs/8.4/interactive/indexes-expressional.html>.  Most of the time conditional indexes are the real ticket.



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