PostgreSQL – How to Repeat an Action for Multiple Tables

postgresqlpostgresql-9.6

Note: The solution does not need be optimised for processing speed. It's supposed to be part of a setup phase and won't be run more than once.

General problem

I have X number of tables in a PostgreSQL database with very different names. I can neither be bothered to write the same PostgreSQL commands for each of them, nor can I be sure that the next set of data that I import has the exact same set of tables. Yet I would like to repeat the same action on each of them. How would I go about doing this? Is it even possible?

I will accept an answer that can do this much. However, an answer that does this and also allows me to exclude some tables that I do know the names of would be even better.

Specifics

This question is closely related to another question which details what I want to do with the tables, but both specific and more general answers are appreciated.

Findings

While looking for a solution I found this question, but I don't know if and how I could use those answers. I'm including it here in hopes that someone else might know how to apply them to my question.

Best Answer

Thanks to the hint given in the comments by @a_horse_with_no_name I've found the answer in the documentation for the DO command: https://www.postgresql.org/docs/9.6/static/sql-do.html

CREATE TABLE new_table ();
DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT table_name FROM information_schema.tables
         WHERE table_schema = 'public' AND table_type = 'BASE TABLE' AND table_name <> ALL (string_to_array('merge_table,spatial_ref_sys', ','))        LOOP
        -- Stuff that is specific to what I want to do
        EXECUTE 'CREATE TEMP TABLE temp_table AS SELECT * FROM ' || quote_ident(r.table_name) || ' NATURAL FULL OUTER JOIN new_table';
        EXECUTE 'DROP TABLE new_table CASCADE';
        EXECUTE 'CREATE TABLE new_table AS SELECT * FROM temp_table';
        EXECUTE 'DROP TABLE temp_table CASCADE';
        EXECUTE 'DROP TABLE ' || quote_ident(r.table_name) || ' CASCADE';
        -- End of the specific stuff
    END LOOP;
END$$;

Thanks to @a_horse_with_no_name for the suggestion on simplifying the exclusion of specific tables.

EDIT:

I found an even better way to exclude specific tables which at least works for my purposes. What I do is that I run the following query before I import any of the tables that I wish to perform the repeated action on:

DO $$BEGIN
    DROP TABLE IF EXISTS tables;
    CREATE TABLE tables (table_name text);
    INSERT INTO tables (
        SELECT table_name
         FROM information_schema.tables
         WHERE table_schema = 'public'
          AND table_type = 'BASE TABLE'
        );
END$$;

This creates a table containing the names of all the tables (including itself). More tables can of course be added manually with INSERT INTO tables VALUES ('new_table');.
Then in the WHERE clause of the FOR loop, instead of doing table_name <> ALL (string_to_array('merge_table,spatial_ref_sys', ',')) I do table_name NOT IN (SELECT table_name FROM tables). This is much easier to maintain than changing the string whenever new tables to be excluded are introduced.

Related Question