Process through all the tables in postgresql stored procedures

Question

I need to loop through all the table in a database in postgresql. is there a similar stored procedure like mssql sp_msforeachtable for postgresql?

mu is too short · Accepted Answer · 2011-06-22 22:02:18Z

3

I usually use this query against information_schema.tables:

SELECT table_name
FROM information_schema.tables
WHERE table_type   = 'BASE TABLE'
  AND table_schema = 'public'
ORDER BY table_name

You might want to adjust the table_schema to suit your needs though. This query should (AFAIK) work in any database that follows the standard.

answered Jun 22, 2011 at 22:02

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tobu · Accepted Answer · 2011-06-22 21:35:33Z

2

Look in the information schema: information_schema.tables.

answered Jun 22, 2011 at 21:35

Tobu

25.6k4 gold badges94 silver badges100 bronze badges

2 Comments

technomage Over a year ago

How do I loop through all the tables then process the table, if I lookup in the information_schema.tables I still need to use a script to loop the returned results and execute / process each table. In ms sql you can just do something like this in the command line (sqlcmd) : sp_msforeachtable 'delete from ?' that command will delete columns from all the tables in a database

Sean Over a year ago

@technomage, you need to learn how to use UNIX. psql -At -c 'SELECT table_name FROM information_schema.tables' | xargs -n 1 ....

Collectives™ on Stack Overflow

Process through all the tables in postgresql stored procedures

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related