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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, May 4, 2008 at 12:31 AM, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

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

Of course. This is the a limitation stemming from the CakePHP web
development framework. In this case, it would assign those two fields to an
unnamed table/bucket, but for columns stemming from the from clause, it
splits them into buckets based on the table alias name. I can alias the
table names, but it means rewriting all the custom queries in the app, or
the resulting processing code which has been relying on the bucketizing.
Which is what I will do. Just another small speedbump moving my app away
from MySQL.

Scott