Learning PostgreSQL 11 - Third Edition

Executing DDL statements in dynamic SQL

In some cases, you will need to perform operations at the database object level, such as tables, indexes, columns, and roles. For example, suppose that a database developer would like to vacuum and analyze a specific schema object, which is a common task after the deployment, in order to update the statistics. To analyze the car_portal_app schema tables, we could write the following script:

DO $$DECLARE  table_name text;BEGIN    FOR table_name IN SELECT tablename FROM pg_tables WHERE schemaname ='car_portal_app' LOOP        RAISE NOTICE 'Analyzing %', table_name;        EXECUTE 'ANALYZE car_portal_app.' || table_name;    END LOOP;END;$$;