DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=YdXftLH3V5y6fy02RiDxHIwYnhsfMrXCk79TrW9ZMeFcfBMQ7QNv8Fj/IX0p75elo37QiI6D9kW47nQIMewpQPJXjvzQEPHb6zJnmZ4/kfJARvdMubu6PmxHmqRFqkPqr+grZ4g0gWjw5RVFLm6qOvBLUaUnqccrT1d6dXYk39s=
Message-ID: <aa44f4a30510041250k3ea4e067r424d66ae2461453@mail.gmail.com>
Date: Tue, 4 Oct 2005 15:50:39 -0400
From: solarsail <solarsail@gmail.com>
Reply-To: solarsail <solarsail@gmail.com>
To: Yasir Malik <ymalik@cs.stevens.edu>
Subject: Re: using pg_tables and tablename in queries
Cc: PostgreSQL <pgsql-sql@postgresql.org>
In-Reply-To: 
 <Pine.NEB.4.62.0510041518300.20189@long-trail.cs.stevens-tech.edu>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
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>

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
=09query :=3D 'auditrecord';

=09FOR
=09=09atablename IN select  * from pg_tables where tablename like 'mytable_=
%'
=09LOOP
=09=09
=09=09query :=3D query || ', ' || quote_ident(atablename.tablename);

=09END 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 tab=
le we
> > found it was much faster to perform inserts on a smaller table. Our cur=
rent
> > 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 pe=
rform
> > queries against my huge set of data to generate some reports. I'm proba=
bly
> > going to create a temporary table with a few indexes to make the report=
s 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 include=
s
> 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 nam=
e
> you need.
>
> Regards,
> Yasir
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>