From: solarsail <solarsail@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: using pg_tables and tablename in queries
Date: Fri, 7 Oct 2005 15:54:59 -0400
Message-ID: <aa44f4a30510071254p46064ca0vff639cd492b22c0b@mail.gmail.com> (raw)
In-Reply-To: <di0r63$2qgl$1@news.hub.org>
References: <aa44f4a30510040930m510bbe8do4babd97b1b37c90d@mail.gmail.com>
<9420.1128445291@sss.pgh.pa.us>
<di0r63$2qgl$1@news.hub.org>
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
( 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....
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: solarsail@gmail.com
Subject: Re: using pg_tables and tablename in queries
In-Reply-To: <aa44f4a30510071254p46064ca0vff639cd492b22c0b@mail.gmail.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