CODEWITHSUNDEEP
sqlpostgresql
Daniel K
Daniel K

Reputation: 31

PostgreSQL, Getting column names from joined table

I was manually specifying column names whenever I make some changes in my db query in my browser. Then, I wanted this procedure to be generic, so I decided to make a query for column names.

So far I have read through some other threads, seems like following code works fine for plain tables without any joins.

select column_name
from information_schema.columns
where table_name='table_name';

However, I wanted to get a column names from inner joined table. Is there a way to achieve what I want?

Here is a sample from my current project, I join three tables using inner join query.

select * 
from analytic 
inner join (select * from session_analytic 
    inner join  (select session_uid from title_session where title_uid = '1234')
    as n2 using(session_uid))
as n3 using(analytic_uid);

as result of the query I get

--------------------------------------------------------------------------------
|analytic_uid | total_playtime | total_frame | total_score | layer1_score |... |
|-------------|----------------|-------------|-------------|--------------|----|
|1            |         21.694 |         471 |           1 |           20 |... |
|2            |          22.92 |         494 |           1 |            0 |... |
|3            |          40.92 |         329 |           1 |            0 |... |
|4            |         15.642 |         900 |           1 |            1 |... |
|5            |        120.754 |        2400 |           0 |            0 |... |
|6            |          5.878 |         323 |           1 |            0 |... |
|7            |         26.177 |         614 |           0 |            0 |... |
|8            |         21.959 |         517 |           1 |            0 |... |
|9            |         10.759 |         227 |           1 |            0 |... |
|10           |         35.458 |        2035 |           0 |            0 |... |
--------------------------------------------------------------------------------

I want to retrieve column names with a query. So I can get something like this. Format doesn't really matter at the moment.

[analytic_uid, total_playtime, total_frame, total_score, layer1_score, ...]

Upvotes: 0

Views: 3898

Answers (1)

Neo X
Neo X

Reputation: 957

You can use the inner joined table as a newly created table, then get its columns name as usual in a subquery style.

select column_name from 
    information_schema.columns
    where
    (select table1, table2) as inner_joined_table;

Upvotes: 2

Related Questions