How to change schema of multiple PostgreSQL tables in one operation?

Question

I have a PostgreSQL 9.1 database with 100 or so tables that were loaded into the 'public' schema. I would like to move those tables (but not all of the functions in 'public') to a 'data' schema.

I know that I can use the following to move 1 table at a time.

ALTER TABLE [tablename] SET SCHEMA [new_schema] 

Is it possible to move all of the tables to the new schema in one operation? If so, what would be the most efficient way to accomplish this task?

Answer 1

DO will do the trick:

DO $$ DECLARE     row record; BEGIN     FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' -- and other conditions, if needed     LOOP         EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || ' SET SCHEMA [new_schema];';     END LOOP; END; $$;