How do I pass a list of tables from a view to FROM?-CodePudding

There is such a task: through information_schema.tables to get a list of tables that satisfy a given condition. Then make a SELECT query to the combined set of those tables that are included as tuples in the previously obtained representation. Example:

SELECT TABLE_NAME
FROM information_schema.tables
WHERE table_schema='my_scheme';

Conditional result:

table1
table2
table3

Now I need to write code that would be equivalent to the query:

SELECT attribute1
FROM table1, table2, table3

CodePudding user response：

You can achieve that by a function as

create or replace function select_from_tables()
returns setof varchar as --depending on your attribute1 type 
$$
DECLARE
    table_name text;
    returning_tables text;
BEGIN
    FOR table_name IN SELECT t.table_name FROM information_schema.tables t WHERE table_schema  ='public' LOOP
        returning_tables:= concat(returning_tables,table_name,' ');
    END LOOP;
    return query EXECUTE 'select attribute1 from '||returning_tables; --substitute attribute1 with your column name
END;
$$ language plpgsql;

and execute

select select_from_tables();