PostgreSQL: Using schema and table names from other tables?

Question

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?

Answer 1

Jack has demonstrated the way to go. However, I feel there is room for improvement.

Test setup:

-- meta tables for schema and table name 
CREATE TABLE schma(schma_id int, schma text);
INSERT INTO schma VALUES (1, 'x');
CREATE TABLE tbl(tbl_id int, tbl text);
INSERT INTO tbl VALUES (1, 't1'), (2, 't2');
-- dummy tables to be used in example query:
CREATE TABLE t1(id int);
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2(foo text);
INSERT INTO t2 VALUES ('some text'), ('some more text');

Function updated with format() in 2017; formatted in 2021:

CREATE OR REPLACE FUNCTION f_dynaquery(_schma_id int
 , _tbl_id int
 , _col text
 , _type anyelement)
 RETURNS TABLE(col anyelement)
 LANGUAGE plpgsql AS
$func$
BEGIN
 RETURN QUERY EXECUTE format(
 'SELECT %I FROM %I.%I'
 , _col
 , (SELECT schma FROM schma WHERE schma_id = _schma_id)
 , (SELECT tbl FROM tbl WHERE tbl_id = _tbl_id)
 );
END
$func$;
COMMENT ON FUNCTION f_dynaquery(int, int, text, anyelement)
IS 'Query any column from a dynamically assembled tablename.
1ドル .. id of schema
2ドル .. id of table
3ドル .. name of column
4ドル .. type of column (only data type matters, not the value)';

db<>fiddle here

Call:

SELECT col FROM f_dynaquery(1, 1, 'id', NULL::int);
 col
-----
 1
 2
SELECT col FROM f_dynaquery(1, 2, 'foo', NULL::text);
 col
----------------
 some text
 some more text

The function can return any column of any type.
Read the manual about Polymorphic Types and Declaring Function Parameters.

Use format() to defend against SQL injection. See:

• PL/pgSQL regclass quoting of table named like keyword

Retrieve the table name by query as requested.

Name the returned column, so it is easier to reference.

Original answer:

CREATE OR REPLACE FUNCTION f_dynaquery_old(int, int, _col text, _type anyelement, OUT col anyelement)
 RETURNS SETOF anyelement
 LANGUAGE plpgsql AS
$func$
BEGIN
 RETURN QUERY EXECUTE '
 SELECT ' || quote_ident(_col) || '
 FROM ' || (
 (SELECT schma FROM schma WHERE schma_id = 1ドル) || '.' ||
 (SELECT tbl FROM tbl WHERE tbl_id = 2ドル))::regclass;
END
$func$;

Defend against SQL injection by using quote_ident() and casting to regclass.

• Erwin Brandstetter: @JackDouglas: I built on your example, so you might be interested in this one.
• Jack Douglas: I found this by chance - the '@' won't reach me if I'm not the poster unless I've commented on the post! Thought you'd like to know :)
• Erwin Brandstetter: @JackDouglas: Ah, I didn't know that such a comment doesn't reach you. Thanks for the info!

Answer 2

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