Date: Tue, 4 Oct 2005 15:23:15 -0400 (EDT)
From: Yasir Malik <ymalik@cs.stevens.edu>
To: PostgreSQL <pgsql-sql@postgresql.org>
Subject: Re: using pg_tables and tablename in queries
In-Reply-To: <aa44f4a30510041140s1f552a78yfa44cb287144ad2b@mail.gmail.com>
Message-ID: <Pine.NEB.4.62.0510041518300.20189@long-trail.cs.stevens-tech.edu>
References: <aa44f4a30510040930m510bbe8do4babd97b1b37c90d@mail.gmail.com>
	<9420.1128445291@sss.pgh.pa.us>
	<aa44f4a30510041140s1f552a78yfa44cb287144ad2b@mail.gmail.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

> The current behavior is by design.
>
> We use the table as a logging repository. It can get very large 250 000
> records. Because of the large number of records that we have in the table we
> found it was much faster to perform inserts on a smaller table. Our current
> system rolls the tables over every 12 hours or so, creating a new table with
> the following behavior:
>
> CREATE TABLE mytable_temp {...}
>
> ALTER TABLE mytable RENAME TO mytable_back_datetime;
> ALTER TABLE mytable_temp RENAME TO mytable;
>
> I want to join the mytable_back_datetime tables together in order to perform
> queries against my huge set of data to generate some reports. I'm probably
> going to create a temporary table with a few indexes to make the reports run
> faster... however I need to join the tables all together first.
>

I would create a function that creates a string with a query that includes 
all the tables you need, and call execute on the string.  You would loop 
through the all tables from pg_tables and keep on appending the table name 
you need.

Regards,
Yasir