Return keys for values with dot-separation in joined statements

Started by Alexander Reichstadtabout 14 years ago3 messagesgeneral
Jump to latest

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Alexander Reichstadt (#1)
Re: Return keys for values with dot-separation in joined statements

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

In reply to: Tom Lane (#2)
Re: Return keys for values with dot-separation in joined statements

PGSQLKit for Cocoa.

Am 12.03.2012 um 18:09 schrieb Tom Lane:

Show quoted text

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