From: "Jim Buttafuoco" <jim@contactbda.com>
To: solarsail <solarsail@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Reply-To: jim@contactbda.com
Subject: Re: using pg_tables and tablename in queries
Date: Tue, 4 Oct 2005 15:45:07 -0400
Message-Id: <20051004194359.M25426@contactbda.com>
In-Reply-To: <aa44f4a30510041140s1f552a78yfa44cb287144ad2b@mail.gmail.com>
References: <aa44f4a30510040930m510bbe8do4babd97b1b37c90d@mail.gmail.com>
	<9420.1128445291@sss.pgh.pa.us>
	<aa44f4a30510041140s1f552a78yfa44cb287144ad2b@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset=iso-8859-1

do you mean UNION ALL instead of JOIN,  if you mean UNION ALL , I would go with a set returning function passing it 
the necessary WHERE clause to be applied to all of your tables.  You might be able to wrap the whole thing into a view


---------- Original Message -----------
From: solarsail <solarsail@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Sent: Tue, 4 Oct 2005 14:40:54 -0400
Subject: Re: [SQL] using pg_tables and tablename in queries

> 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.
> 
> On 10/4/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > solarsail <solarsail@gmail.com> writes:
> > > I have a large number of tables with a common naming convention
> >
> > > mytable001, mytable002, mytable003 ... mytable00n
> >
> > > I would like to do a query across all of the tables, however I do not
> > know
> > > all of the tables before hand, and I do not want to ( cant ) manually
> > > generate a query like
> >
> > > select * from mytable001, mytable002, mytable003
> >
> > > I have a query that returns the names of the tables I want to query:
> >
> > > select tablename from pg_tables where tablename like 'mytable%'
> >
> > This looks to me like a situation in which you should rethink your
> > data design. Those tables should all get merged into one big table,
> > adding one extra column that reflects what you had been using to
> > segregate the data into different tables.
> >
> > regards, tom lane
> >
------- End of Original Message -------