From: Jim Buttafuoco <jim@contactbda.com>
To: solarsail <solarsail@gmail.com>
To: Yasir Malik <ymalik@cs.stevens.edu>
Cc: PostgreSQL <pgsql-sql@postgresql.org>
Subject: Re: using pg_tables and tablename in queries
Date: Tue, 4 Oct 2005 21:01:12 -0400
Message-ID: <20051005005916.M89424@contactbda.com> (raw)
In-Reply-To: <aa44f4a30510041250k3ea4e067r424d66ae2461453@mail.gmail.com>
References: <aa44f4a30510040930m510bbe8do4babd97b1b37c90d@mail.gmail.com>
<9420.1128445291@sss.pgh.pa.us>
<aa44f4a30510041140s1f552a78yfa44cb287144ad2b@mail.gmail.com>
<Pine.NEB.4.62.0510041518300.20189@long-trail.cs.stevens-tech.edu>
<aa44f4a30510041250k3ea4e067r424d66ae2461453@mail.gmail.com>
again, do you really want to join the tables or do a UNION ALL. From one of your posts you said the table were the
same.
you need to do something like
select * from table_001
union all
select * from table_002
...
select * from table_999
I would do this in a set returning function looping of an EXECUTE. If you need help, post the schema for a couple of
your tables and I will help with the function
Jim
---------- Original Message -----------
From: solarsail <solarsail@gmail.com>
To: Yasir Malik <ymalik@cs.stevens.edu>
Cc: PostgreSQL <pgsql-sql@postgresql.org>
Sent: Tue, 4 Oct 2005 15:50:39 -0400
Subject: Re: [SQL] using pg_tables and tablename in queries
> I managed to make this work as sub query before... I wish I had
> written it down somewhere...
>
> Regarding the creation of a function. I do have a function that
> almost does that. I'm having a hard time getting it to return a set
> of records from the EXECUTE command ( more than one row returned by
> the select * ...).
>
> If I generate a temporary table instead of returning the results how
> long will that table exist for? Excuse the OOP terminology but would
> it be correct to create a 'Singleton' to access the temporary table,
> where if it exists and is less than 30 minutes old use that one,
> otherwise drop the table and recreate it?
>
> Thanks
>
> -- sample function..
>
> CREATE OR REPLACE FUNCTION testfunc_jointables()
> RETURNS SETOF record AS
> $BODY$
> DECLARE
> query TEXT;
> BEGIN
> query := 'auditrecord';
>
> FOR
> atablename IN select * from pg_tables where tablename like 'mytable_%'
> LOOP
>
> query := query || ', ' || quote_ident(atablename.tablename);
>
> END LOOP;
>
> EXECUTE ' SELECT * from ' || query;
>
> END;
>
> On 10/4/05, Yasir Malik <ymalik@cs.stevens.edu> wrote:
> > > 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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
------- End of Original Message -------
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: jim@contactbda.com, solarsail@gmail.com, ymalik@cs.stevens.edu
Subject: Re: using pg_tables and tablename in queries
In-Reply-To: <20051005005916.M89424@contactbda.com>
* 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