Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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

DROP SCHEMA x CASCADE;
CREATE SCHEMA x;

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');

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');

CREATE OR REPLACE FUNCTION x.f_dynaquery(int, int, _col text, _type anyelement, OUT col anyelement)
  RETURNS SETOF anyelement AS
$body$
BEGIN

RETURN QUERY EXECUTE '
SELECT ' || quote_ident(_col) || '
FROM   ' || (
    (SELECT schma FROM x.schma WHERE schma_id = $1)
     || '.' ||
    (SELECT tbl   FROM x.tbl   WHERE tbl_id   = $2))::regclass;

END;
$body$
  LANGUAGE plpgsql STABLE;

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 (actual type matters, not the values)';

Call:

SELECT col FROM x.f_dynaquery(1, 1, 'id', NULL::int);
 col
-----
   1
   2
(2 rows)

SELECT col FROM x.f_dynaquery(1, 2, 'foo', NULL::text);
  col
----------------
 some text
 some more text
(2 rows)

Major points

share|improve this answer
    
@JackDouglas: I built on your example, so you might be interested in this one. –  Erwin Brandstetter Nov 9 '11 at 6:42
1  
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 :) –  Jack Douglas Nov 9 '11 at 10:36
    
+1 very interesting and useful points –  Jack Douglas Nov 9 '11 at 10:41
    
@JackDouglas: Ah, I didn't know that such a comment doesn't reach you. Thanks for the info! –  Erwin Brandstetter Nov 9 '11 at 15:52
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.