Re: running a query on a list of tables

On 29 October 2018 at 16:48, Mark King <fires10(at)gmail(dot)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