pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
query tables based on a query 2+ messages / 2 participants [nested] [flat]
* query tables based on a query @ 2005-09-30 22:47 solarsail <solarsail@gmail.com> 2005-10-24 17:02 ` Re: query tables based on a query Bruno Wolff III <bruno@wolff.to> 0 siblings, 1 reply; 2+ messages in thread From: solarsail @ 2005-09-30 22:47 UTC (permalink / raw) To: pgsql-sql I have a large number of tables with a common naming convention basically: table001, table002, table003 ... table00n 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 manually generate a query like select * from table001, table002, table003 I have a query that returns the names of the tables I want to query, it is basically like this: select tablename from pg_tables where tablename like 'table%' How do I do this? I've tried creating a Table Function that returns the above set and tried to use that in a select clause, but I cant get it to work either. Thanks for the help ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: query tables based on a query 2005-09-30 22:47 query tables based on a query solarsail <solarsail@gmail.com> @ 2005-10-24 17:02 ` Bruno Wolff III <bruno@wolff.to> 0 siblings, 0 replies; 2+ messages in thread From: Bruno Wolff III @ 2005-10-24 17:02 UTC (permalink / raw) To: solarsail <solarsail@gmail.com>; +Cc: pgsql-sql On Fri, Sep 30, 2005 at 18:47:48 -0400, solarsail <solarsail@gmail.com> wrote: > I have a large number of tables with a common naming convention > > basically: > > table001, table002, table003 ... table00n > > > 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 manually generate a > query like > > select * from table001, table002, table003 > > > I have a query that returns the names of the tables I want to query, it is > basically like this: > > select tablename from pg_tables where tablename like 'table%' > > > How do I do this? I've tried creating a Table Function that returns the > above set and tried to use that in a select clause, but I cant get it to > work either. The simplest way to do this is to have the application use the results of the above query to write a query using UNION ALL that selects from each of the tables. Also, unless you have a good reason to partition your data, you may want to consider a design where all of this data is in one table. > > > Thanks for the help ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-10-24 17:02 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-09-30 22:47 query tables based on a query solarsail <solarsail@gmail.com> 2005-10-24 17:02 ` Bruno Wolff III <bruno@wolff.to>
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