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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
