How to get a list of tables with table name matching a pattern in Postgres

I wanted to write a Postgres function to loop through a bunch of tables and perform the same procedure on them. The tables are selected by the table name, starts with dmt_. The procedure I want to do on each table is pretty simple, just get the number of latest records in last few days. The problem how to get the tables with matching table name.

Answer

You can query information_schema for this.

SELECT table_schema, table_name 
FROM information_schema.tables 
WHERE table_name ~ '^dmt_'`

Once you have the schema & name, you can manipulate them as required in plpgsql or other procedural languages.