X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 89978D9BA9
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  7 Oct 2005 16:55:02 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 82790-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  7 Oct 2005 19:54:58 +0000 (GMT)
Received: from qproxy.gmail.com (qproxy.gmail.com [72.14.204.200])
	by svr1.postgresql.org (Postfix) with ESMTP id 59C39D83C7
	for <pgsql-sql@postgresql.org>; Fri,  7 Oct 2005 16:54:58 -0300 (ADT)
Received: by qproxy.gmail.com with SMTP id v28so712707qbe
	for <pgsql-sql@postgresql.org>; Fri, 07 Oct 2005 12:54:59 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=EVGGZbIEnuWtsdfH/kBbcaKIZaN3lKfepDtf1Nlmr4j3nSUOGQd1Mj7frFXHfXgF8tywmBGX51CfuiZyyKeryAsM464xPiufdD7x7K1SyPklGrLSKo9kr9nKtCwOVjdU6zWhmwf6g3fkZGN+cyH36okqRK1bgidB1ALKE1fDs6s=
Received: by 10.65.53.10 with SMTP id f10mr1668521qbk;
	Fri, 07 Oct 2005 12:54:59 -0700 (PDT)
Received: by 10.65.158.12 with HTTP; Fri, 7 Oct 2005 12:54:59 -0700 (PDT)
Message-ID: <aa44f4a30510071254p46064ca0vff639cd492b22c0b@mail.gmail.com>
Date: Fri, 7 Oct 2005 15:54:59 -0400
From: solarsail <solarsail@gmail.com>
Reply-To: solarsail <solarsail@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: using pg_tables and tablename in queries
In-Reply-To: <di0r63$2qgl$1@news.hub.org>
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> <di0r63$2qgl$1@news.hub.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.877 required=5 tests=[AWL=0.853, RCVD_BY_IP=0.024]
X-Spam-Level: 
X-Archive-Number: 200510/73
X-Sequence-Number: 22926

Thanks for the help everyone.  I got everything working.

The query I was orignally looking for was something like this:

SELECT  * from mytable WHERE pg_tables.tablename IN
=09=09( select tablename from pg_tables where tablename like 'mytable _%' )

It turns out that isn't what I really wanted.

I now have a nice stored procedure, with a clean interface.  Thanks
for all the help!



On 10/5/05, codeWarrior <gpatnude@hotmail.com> wrote:
>
> "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
> news:9420.1128445291@sss.pgh.pa.us...
> > 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
>
> Sounds like a classic opportunity to use the postgreSQL inheritance model=
 to
> me....