You should be able to just run select * from information_schema.tables to get a listing of every table being managed by Postgres for a particular database.

You can also add a where table_schema = 'information_schema' to see just the tables in the information schema.

The views in the INFORMATION_SCHEMA are defined by the SQL standard and display information that is required by that. So they can't display any Postgres specific information that doesn't go along with the rules of the SQL standard. So queries using that are likely to work on other DBMS products as well that support INFORMATION_SCHEMA Not all products implement it 100% correct though. Postgres also has some areas where it deviates from the specification of the INFORMATION_SCHEMA. But the similarities are close enough that it's really easy to port and use such a query with a different database.

All system tables and views in the pg_catalog schema (including pg_tables) are completely Postgres specific. Queries using those will never run on other DBMS products. The INFORMATION_SCHEMA views use those system views and tables to collect and present the metadata as required by the SQL standard.

Sounds like you need dynamic SQL for this. First build your query using string concatenation with the desired values (e.g. schema name, table name), then execute it with EXECUTE.

Example:

    -- create example objects
    CREATE SCHEMA example_schema;
    CREATE TABLE example_schema.example_table1 (example_column integer);
    CREATE TABLE example_schema.example_table2 (example_column integer);
    CREATE TABLE example_schema.example_table3 (example_column integer);

    -- example anonymous block using dynamic queries
    DO
    $$
    DECLARE
      dynamic_query text;
      catalog_row record;
      counter integer = 0;
    BEGIN
      -- insert an integer, that's incremented for every iteration into the example tables
      FOR catalog_row IN SELECT table_schema || '.' || table_name qualified_table_name
                                FROM information_schema.tables
                                WHERE table_schema = 'example_schema'
                                      AND table_name LIKE 'example_table%' LOOP
        counter := counter + 1;
        -- building the query;
        dynamic_query := 'INSERT INTO ' || catalog_row.qualified_table_name || ' VALUES (' || counter || ')';
        -- executing the query;
        EXECUTE dynamic_query;
      END LOOP;
    END;
    $$
    LANGUAGE plpgsql;

    -- check if it has worked
    SELECT * FROM example_schema.example_table1
    UNION ALL
    SELECT * FROM example_schema.example_table2
    UNION ALL
    SELECT * FROM example_schema.example_table3;