Query from tables where the table names are stored on a table

Question

I have a table called schemas, it has:

unique_id (varchar) - contains table names
data (text)

each unique_id on the schemas rows has a table with similar columns, for example: users (id, data), holidays (id, data), etc..

Is it possible to query from those tables without typing one by one?

CREATE TABLE schemas (
  unique_id VARCHAR(32) PRIMARY KEY, // table names stored here
  data TEXT
);
INSERT INTO schemas VALUES('users','foo'),('holidays','bar');
-- not just users and holidays, there are many more..  

CREATE TABLE users ( 
  id BIGSERIAL,
  data TEXT
);
INSERT INTO users(data) VALUES('user1'),('user2'); 

CREATE TABLE holidays (
  id BIGSERIAL,
  data TEXT
);
INSERT INTO holidays(data) VALUES('hol1'),('hol2');
-- and so on, if a record added to the schemas, 
--   a new table created with unique_id name

Is it possible to do a single query without PL/* that could give a result something like this:

schema_unique_id | schema_data | table_data
users            | foo         | user1
users            | foo         | user2
holidays         | bar         | hol1
holidays         | bar         | hol2

Yes, it is possible. But: if your table- or column names are variables you'll need dynamic SQL. (which at least implies plpgsql) — wildplasser
– wildplasser, Commented Feb 11, 2015 at 12:26
There is no way to dynamically generate SQL without some sort of non-SQL programming - whether that's PL/* or a program talking to your database. — Ben Grimm
– Ben Grimm, Commented Feb 11, 2015 at 12:26

Ben Grimm · Accepted Answer · 2015-02-11 13:03:55Z

It's typically not possible without dynamic SQL. But, with your specified use-case, it can be realized using inheritance:

CREATE TABLE schemas (
  unique_id VARCHAR(32) PRIMARY KEY, 
  data TEXT
);
INSERT INTO schemas VALUES('users','foo'),('holidays','bar');

CREATE TABLE mydata (
  id BIGSERIAL,
  data text
);

CREATE TABLE users ( ) INHERITS (mydata);
INSERT INTO users(data) VALUES('user1'),('user2'); 

CREATE TABLE holidays ( ) INHERITS (mydata); 
INSERT INTO holidays(data) VALUES('hol1'),('hol2');

SELECT s.unique_id, s.data as schema_data, d.data as table_data
FROM schemas s, mydata d, pg_class
WHERE d.tableoid = pg_class.oid AND s.unique_id = pg_class.relname;

 unique_id | schema_data | table_data 
-----------+-------------+------------
 holidays  | bar         | hol1
 holidays  | bar         | hol2
 users     | foo         | user1
 users     | foo         | user2
(4 rows)

SELECTing from the parent table causes PostgreSQL to UNION the rows of the parent with queries of those inherited columns from each of the child tables.

If your tables are already compatible with the inheritance model, you can ALTER them to introduce it:

ALTER TABLE users INHERIT mydata;

Collectives™ on Stack Overflow

Query from tables where the table names are stored on a table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related