On Fri, May 2, 2008 at 9:34 AM, Scott Miller <smiller@duels.com> wrote:
> One problem I've had in development recently is the inability to get the
> aliased name of a table from a query. We're using a PHP framework for
> querying, which internally uses pg_field_name to retrieve the select list
> field name, which is great. There is alwo pg_table_name, to retrieve the
> table the field originated from. The problem is that this returns the name
> of the table, unaliased. If a query does a self join, you can't retrieve a
> distinguishing table alias name for that field. For example:
>
> SELECT a.mycolumn, b.mycolumn
> FROM mytable a, mytable b
> WHERE ...
>
> If I controlled query generation at all times, I could simply alias
> a.mycolumn and b.mycolumn differently in the select list. But if for
> example this is SELECT * FROM, those columns are indistinguishable.
You have the same type of problem if you have this query:
select count(id), count(int2) from table.
They both are named count. The simple answer is to always alias your
select fields.
select count(id) as idcount, count(int2) as intcount from table.
or
SELECT a.mycolumn as a_mycol, b.mycolumn as b_mycol
FROM mytable a, mytable b
WHERE ...