Update a view with daily tables in Postgres I have a view like this:

CREATE OR REPLACE VIEW some_table AS
select * from h20180101
union all
select * from h20180102
union all
select * from h20180103
etc...


It's a union daily tables. The thing is each day a new table is created. Is there a way in PL/PgSQL to automatically update the script daily to make a union to the table of the previous day?

Thanks!
If you are on postgresql 10, the best way to handle this is to use partitioning so you have a single table instead of a view. On lower versions you can do partitioning, but it is not declarative (i.e. you have to do a lot of the work yourself).

Can't think of a way to do this from within Postgres/Pl/PgSQL, but you could do something like this:


Create a shell script that you run in psql to output the list of tables. Something like psql -c "\dt h2018*" or the equivalent query looking at system tables
Use the output to send it to AWK, or your favorite scripting language, and then build a new .sql file and then call that with psql. You likely can do it with a scripting language completely to build the query, but likely the above is the easiest and fastest way to accomplish what you want.