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
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.
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'