Postgres subquery in FROM clause

Question

In Postgres, I'm using a subquery in a FROM clause, but don't really know how to get the result I want. Say I have 2 tables, tableNameRegister(idNum integer, tableName text) and mytable(comment text, orderVal integer). The tableNameRegister would look something like this:

 idnum | tablename
-------+------------
     1 | mytable
     2 | othertable

And mytable would look something like this:

  comment  | orderval
-----------+-------
 comment 1 |     1
 comment 2 |     2

What I want to to is take the tableName value from tableNameRegister, and select from that tableName value, but all in one query. Essentially, something like this:

tabName = 'SELECT tableName FROM tableNameRegister WHERE idNum = 1;'
'SELECT * FROM ${tabName} WHERE orderVal = 2'

Ideally this would return the row containing comment 2. So I tried doing it in a subquery:

'SELECT * FROM (SELECT tableName FROM tableNameRegister WHERE idNum = 1) AS tabname WHERE orderVal = 2;'

But found out but this doesn't work the way I thought it would. Essentially, it returns a sort of subtable based off the results of the subquery, and not the actual value of mytable that I want. I was wondering if there is a way to do what I intended, all in one query/using subqueries? Or is this something I would have to do programmatically and seperate into two queries?

scottjustin5000 · Accepted Answer · 2019-03-22 23:29:08Z

1

This type of thing is best done through some sort of scripting or otherwise programmatically. With something like plpgsql we have more flexibility, but even with this, the tricky thing will be if all the tables do not share the same structure or at least common column names. If they did or could be abstracted in some way something like the following could get you started:

CREATE OR REPLACE FUNCTION tablequery(id_num int)
RETURNS TABLE (comment varchar, orderval int)
AS $$
DECLARE
tableName varchar(50);
BEGIN
SELECT table_name FROM tableNameRegister WHERE idnum = id_num LIMIT 1 into tableName;
  RETURN QUERY EXECUTE format('SELECT * FROM %I WHERE orderVal = 2;', tableName);
END;
$$ LANGUAGE plpgsql;

select * FROM tableQuery(1);

answered Mar 22, 2019 at 23:29

scottjustin5000

1,35612 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres subquery in FROM clause

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