Create view with dynamically selected tables - is it possible?

Question

I have subsection of tables in my database that share part of a schema and a naming scheme. I'd like to create a view that automatically gathers all of the common columns of these tables into a single place. The catch is that more of these tables may be added from time to time, and I would ideally like to have the view dynamically add these new tables to itself.

For example, say we have three tables

+-----------------+ +-----------------+ +-----------------+
|table1 | |table2 | |table3 |
+-----------------+ +-----------------+ +-----------------+
|id | |id | |id |
|name | |name | |name |
|unique_attribute1| |unique_attribute2| |unique_attribute3|
+-----------------+ +-----------------+ +-----------------+

If I were doing this statically, I would write the view as:

CREATE VIEW unified_table_view AS
SELECT id, name FROM
 (SELECT id, name FROM table1)
 UNION (SELECT id, name FROM table2)
 UNION (SELECT id, name FROM table3);

However, if a new table

+-----------------+
|table4 |
+-----------------+
|id |
|name |
|unique_attribute4|
+-----------------+

is added and I want it to be included in the view, I would have to go an manually add that to the view definition:

CREATE VIEW unified_table_view AS
SELECT id, name FROM
 SELECT id, name FROM table1
 UNION SELECT id, name FROM table2
 UNION SELECT id, name FROM table3
 UNION SELECT id, name FROM table4;

I want to know if it is possible to write the view such that when table4 is added, the view automatically incorporates it, without me having to update the view definition.

Normally if I was doing something like that, I would interrogate information_schema to construct something for EXECUTE, but that is a one-time thing and I would have to run the query again to update the view.

• Jhunter1: Setup a DDL trigger to check if your new table has been created then it can trigger some dynamic SQL that will add to the view?

Answer 1

You cannot do that with a view, because a view is parsed when it is created, and the parse tree is stored. That will lead to an error if a table used in the view does not exist.

What you could do is to write a table function that searches for tables, constructs a query string for each and uses RETURN QUERY EXECUTE to add the results to the output. That would be equivalent to UNION ALL. If you need UNION, you'd use SELECT DISTINCT to query the table function.