From: Yasir Malik <ymalik@cs.stevens.edu>
To: PostgreSQL <pgsql-sql@postgresql.org>
Subject: Re: using pg_tables and tablename in queries
Date: Tue, 4 Oct 2005 15:23:15 -0400 (EDT)
Message-ID: <Pine.NEB.4.62.0510041518300.20189@long-trail.cs.stevens-tech.edu> (raw)
In-Reply-To: <aa44f4a30510041140s1f552a78yfa44cb287144ad2b@mail.gmail.com>
References: <aa44f4a30510040930m510bbe8do4babd97b1b37c90d@mail.gmail.com>
<9420.1128445291@sss.pgh.pa.us>
<aa44f4a30510041140s1f552a78yfa44cb287144ad2b@mail.gmail.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ymalik@cs.stevens.edu
Subject: Re: using pg_tables and tablename in queries
In-Reply-To: <Pine.NEB.4.62.0510041518300.20189@long-trail.cs.stevens-tech.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox