CODEWITHSUNDEEP
sqlpostgresql
Fedour
Fedour

Reputation: 387

Recover table name in SQL query

I think it's a stupid question but is it possible to recover tables names for each columns in a SQL Query ? Example if my query is:

Select e.empno,e.sal,d.deptno
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE e.job = 'Analyst'

I would like to recover something like this

+-------+---------+
| Column|  Table  |
+-------+---------+
|  empno| emp     |
|   sal | emp     |
| deptno| dept    | 
+-------+---------+

I use postgresql

Thank for help

Upvotes: 0

Views: 540

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

Will aliases meet your requirement?

Select e.empno "emp empno"
,e.sal "emp sal"
,d.deptno "dept deptno"

FROM emp e JOIN dept d ON e.deptno = d.deptno
WHERE e.job = 'Analyst'

Upvotes: 0

Evan Carroll
Evan Carroll

Reputation: 1

When a result is set back to the client it comes without any display or formatting. I assume this question from the context of PSQL in which case you're not referring to actually getting the column names of the table, but instead to the display-format of the query result. PSQL calls this expanded display. You can enable it with \x after you start psql, or you can start psql with it using the --expanded flag.

\x
Expanded display is on.

test=# SELECT * FROM ( VALUES (1,2,3),(4,5,6) ) AS t(foo,bar,baz);
-[ RECORD 1 ]
foo | 1
bar | 2
baz | 3
-[ RECORD 2 ]
foo | 4
bar | 5
baz | 6

If you're referring to actually knowing where the columns are being sourced from in the display format you can not do that. SQL only maintains a result set. It does not know where the table that originated the data is.

And, that wouldn't make much sense anyway, what would this show...

SELECT *
FROM (
  SELECT a
  FROM foo
) AS t;

Would it show the table as t or a?

What about this...

SELECT a+1 AS b, 'b' AS a
FROM (
  SELECT a
  FROM foo
) AS t;

You can see that getting silly really fast.

Upvotes: 1

Related Questions