PostgreSQL: Using schema and table names from other tables? Consider the situation where I have all my schema names in one table and all my table names in another table.

Is it possible to do something like in the following (pseudo-code)?

SELECT value FROM (SELECT schema_name FROM schemas).(SELECT table_name FROM tables)


Or do I have to break up the query into three SELECT's?
You need dynamic SQL - perhaps something like this:

create role stack;
create schema authorization stack;
set role stack;

create or replace function f(p_schema in text, p_table in text)
                           returns setof integer language plpgsql immutable as $$
begin
  return query execute 'select value from '||p_schema||'.'||p_table;
end;$$;

create table t1(value integer);
insert into t1(value) values (1);
insert into t1(value) values (2);

create table t2(value integer);
insert into t2(value) values (1);

create table schemas(schema_name text);
insert into schemas(schema_name) values ('stack');

create table tables(table_name text);
insert into tables(table_name) values ('t1');
insert into tables(table_name) values ('t2');
insert into tables(table_name) values ('t1');
insert into tables(table_name) values ('t2');

select f(schema_name, table_name) from schemas cross join tables;
 f
---
 1
 2
 1
(3 rows)


I'm assuming that every table is present in every schema as the question implies