Hi,
I'm curious how do you handle results from multiple tables with
repeated column names. For example:
# CREATE TABLE c1 (id integer PRIMARY KEY, address inet);
# CREATE TABLE c2 (id integer PRIMARY KEY, address text);
# SELECT * FROM c1 JOIN c2 USING (id);
id | address | address
----+---------+---------
(0 rows)
or:
# SELECT * FROM c1, c2 WHERE c1.id=c2.id;
id | address | id | address
----+---------+----+---------
(0 rows)
Now lets say we want access results from PHP/perl/etc using column
names. We have "address" from c1, and the same from c2. We can't even
distinguish which one is from which table.
I see two available possibilities:
1. rename one or each column (eg. prefix with table name), but it's not
always acceptable and makes JOIN ... USING syntax useless (and it's
messy to change to JOIN .. ON for many columns), it would also not work
if we join on the same table twice or more,
2. select each column explicitly:
SELECT c1.id, c1.address AS c1_address, c2.address AS c2.address
but this is nightmare for tables with many columns, especially if the
schema changes frequently.
Someone could say, that if we JOIN on some column, then it's the same
value, but it does not need to be always true -- we can join on
different columns in different queries.
Any other ideas?
3. Suggestion, but it would be probably hard to implement: to make SQL
engine prefix each returned column with table alias. Of course it would
not be a default behavior, but it would be enabled by some session wide
setting.
# SELECT * FROM c1, c2 WHERE c1.id=c2.id;
c1.id | c1.address | c2.id | c2.address
[...]
# SELECT * FROM c1 JOIN c2 USING (id);
??id | c1.address | c2.address
As JOIN returns only one copy of id, it would be hard to decide about
results (could return one copy for each alias like above).
4. Probably also hard to implement, something like:
# SELECT c1.* AS c1_*, c2.* AS c2_* FROM ...
Or maybe 3 or 4 are already there?
Regards,
BK