CODEWITHSUNDEEP
postgresqlsubquery
albert
albert

Reputation: 1348

Postgres subquery in FROM clause

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?

Upvotes: 0

Views: 864

Answers (1)

scottjustin5000
scottjustin5000

Reputation: 1356

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

Upvotes: 1

Related Questions