Обсуждение: running a query on a list of tables

Поиск
Список
Период
Сортировка

running a query on a list of tables

От
Mark King
Дата:
I am trying to figure out how to run a query on a list of tables or views in this case.
I can run :

I can run :

SELECT * FROM tablelist;

which returns a list of tables

names
----------------
mortgage
creditcard
auto
........continued


I want to run :

SELECT * FROM (SELECT * FROM tablelist);


all tables have the same columns and types.

any idea how to make this work? tablelist contains a large number of tables that will change over time and I do not want to manually write a bunch of unions.

any suggestions?

Thank you

 

Re: running a query on a list of tables

От
David Rowley
Дата:
On 29 October 2018 at 16:48, Mark King <fires10@gmail.com> wrote:
> SELECT * FROM tablelist;
>
> which returns a list of tables
>
> names
> ----------------
> mortgage
> creditcard
> auto
> ........continued
>
>
> I want to run :
>
> SELECT * FROM (SELECT * FROM tablelist);
>
>
> all tables have the same columns and types.
>
> any idea how to make this work? tablelist contains a large number of tables
> that will change over time and I do not want to manually write a bunch of
> unions.
>
> any suggestions?

You could just create a parent table then alter all of your tables in
tablelist to INHERIT the parent. You can then simply; SELECT * FROM
theparent; The query planner will automatically combine the results
from each inheriting table.

Documentation in: https://www.postgresql.org/docs/11/static/ddl-inherit.html

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services