On 28/09/2010 23:53, Bartlomiej Korupczynski wrote:
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.
In PHP you can access columns by index, using pg_fetch_array().
However, I think it's better to embrace the pain and use aliases for the
columns with duplicated names - makes your code much easier to read.
You could also create a view which defines the aliases for the columns,
presenting a consistent interface to the PHP code.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@xxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
