Postgres Pro
Downloads
Home   >   mailing lists

Thread: Return keys for values with dot-separation in joined statements

Return keys for values with dot-separation in joined statements

From
Alexander Reichstadt
Date:
Hi,

writing a client frontend I started with mysql and migrated to postgres. Now I found out that there is a difference in the way joined queries are returned:

The query

SELECT persons.id,persons.lastname,persons.firstname,persons.salutation,persons.title,addresses.address1,addresses.address2,addresses.city,addresses.zip FROM persons LEFT JOIN addresses_reference ON persons.id=addresses_reference.refid_persons LEFT JOIN addresses ON addresses_reference.refid_addresses=addresses.id   LIMIT 106 OFFSET 0

The response from postgres in the API:

    address1 = "";
    address2 = "";
    city = "";
    firstname = Olaf;
    id = 5;
    lastname = Taschenbier;
    salutation = Frau;
    title = "";
    zip = "";

The response from mysql:

    persons.address1 = "";
    persons.address2 = "";
    persons.city = "";
    persons.firstname = Olaf;
    persons.id = 5;
    persons.lastname = Taschenbier;
    persons.salutation = Frau;
    persons.title = "";
    persons.zip = "";


This is especially an issue in cases where two tables do have identical fieldnames. What happens in such cases?

Is there a way or some setting on postgres server to tell postgres to use the fieldnames exactly as provided in the select?

Thanks
Alex

Re: Return keys for values with dot-separation in joined statements

From
Tom Lane
Date:
Alexander Reichstadt <lxr@mac.com> writes:
> Is there a way or some setting on postgres server to tell postgres to use the fieldnames exactly as provided in the
select?

You didn't say exactly which "API" you're using, but in libpq you could
look at the PQftable and PQftablecol column property inquiry functions.

            regards, tom lane

Re: Return keys for values with dot-separation in joined statements

From
Alexander Reichstadt
Date:
PGSQLKit for Cocoa.


Am 12.03.2012 um 18:09 schrieb Tom Lane:

> Alexander Reichstadt <lxr@mac.com> writes:
>> Is there a way or some setting on postgres server to tell postgres to use the fieldnames exactly as provided in the
select?
>
> You didn't say exactly which "API" you're using, but in libpq you could
> look at the PQftable and PQftablecol column property inquiry functions.
>
>             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