PostgreSQL: Using schema and table names from other tables?
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
Jack has demonstrated the way to go. However, I feel there is room for improvement.
I place everything in schema x
for convenient testing. Test setup:
DROP SCHEMA x CASCADE;
CREATE SCHEMA x;
-- meta tables for schema and table name
CREATE TABLE x.schma(schma_id int, schma text);
INSERT INTO x.schma VALUES (1, 'x');
CREATE TABLE x.tbl(tbl_id int, tbl text);
INSERT INTO x.tbl VALUES (1, 't1'), (2, 't2');
-- dummy tables to be used in example query:
CREATE TABLE x.t1(id int);
INSERT INTO x.t1 VALUES (1),(2);
CREATE TABLE x.t2(foo text);
INSERT INTO x.t2 VALUES ('some text'), ('some more text');
Old function (original answer):
CREATE OR REPLACE FUNCTION x.f_dynaquery_old(int, int, _col text, _type anyelement, OUT col anyelement)
RETURNS SETOF anyelement 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$ LANGUAGE plpgsql;
Cleaner version with format()
(update 2017):
CREATE OR REPLACE FUNCTION x.f_dynaquery(_schma_id int, _tbl_id int
, _col text, _type anyelement)
RETURNS TABLE(col anyelement) 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$ LANGUAGE plpgsql;
COMMENT ON FUNCTION x.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)';
Call:
SELECT col FROM x.f_dynaquery(1, 1, 'id', NULL::int);
col
-----
1
2
SELECT col FROM x.f_dynaquery(1, 2, 'foo', NULL::text);
col
----------------
some text
some more text
Major points
Function can return any column of any type. Read in the manual about Polymorphic Types and Declaring Function Parameters.
Defend against SQL injection by using
quote_ident()
and casting toregclass
in the old version or withformat()
in the new one. Related:- PL/pgSQL regclass quoting of table named like keyword
Retrieve the tablename by query as OP requested. Example is query by
id
but anything is possible.Name the returned column, so it is easier to reference.