8

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.

ddd
  • 4,665
  • 14
  • 69
  • 125
  • See [here](https://stackoverflow.com/q/2596670/330315) or [here](https://stackoverflow.com/q/34679622/330315) –  Dec 10 '17 at 20:07

3 Answers3

10

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.

Haleemur Ali
  • 26,718
  • 5
  • 61
  • 85
  • I have `for tb in SELECT * FROM information_schema.tables WHERE table_name ~ '^dmt_mas' loop` in my function and got the error `loop variable of loop over rows must be a record or row variable or list of scalar variables` at `tb`, tried `like `dmt%` and same error – ddd Dec 11 '17 at 01:07
0

You can also feed the output of table names to the grep if you are using linux:

psql -d database_name -c "\d" | grep "some_pattern"
Perman Atayev
  • 1
  • 1
-1
SELECT
     relname as table_name, n_live_tup as row_count
FROM
    pg_stat_user_tables
ORDER BY
    n_live_tup DESC;
Antoine
  • 1,393
  • 4
  • 20
  • 26
Lakshmi
  • 1
  • 1
    As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 17 '21 at 07:51