Thread: Feature request

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.

Cheers,
  Scott

"Scott Miller" <smiller@duels.com> writes:
> 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.

Supporting this would require a protocol change (to allow an additional
field in the result description message).  So don't hold your breath :-(

            regards, tom lane

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