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.
-
See here or hereuser330315– user3303152017-12-10 20:07:35 +00:00Commented Dec 10, 2017 at 20:07
Add a comment
|
4 Answers
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.
1 Comment
ddd
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 errorYou 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"
Comments
This works for me
psql -Uyourusername yourdb -h yourhost -p 5432 -c '\dt dmt_*'
ie. the command
\dt dmt_*
Comments
SELECT
relname as table_name, n_live_tup as row_count
FROM
pg_stat_user_tables
ORDER BY
n_live_tup DESC;
1 Comment
Community
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.