Re: How get column-wise table info from an arbitrary query?

On Tue, Dec 4, 2012 at 8:26 PM, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:

> On 05/12/2012 01:11, Raymond O'Donnell wrote:
> > On 05/12/2012 01:04, Kenneth Tilton wrote:
> >> I am porting from MySQL some code that has to take an arbitrary query
> >> involving joins and build up a dictionary (in an HLL talking to Postgres
> >> over a socket) where each column name will be the key. The catch is that
> >> there will be duplicate entries where two joined tables have the same
> >> column such as "id", so I have to get the source table for each column.
> >> Here is a sample query:
> >>
> >> select * from providers p inner join provider_types pt on pt.id
> >> <http://pt.id> = p.provider_type_id;
> >>
> >> I actually figured out how to get the table OID which would suffice, but
> >> I am porting MySQL code that could get fully qualified column names
> >> including a table alias if that were used. We allow other code to look
> >> up values in the dictionary with the alias as a prefix as a convenience,
> >> eg. "p.id <http://p.id>" or "pt.id <http://pt.id>".
> >
> > Maybe I'm misunderstanding, but why don't you just give the columns an
> > alias directly? -
> >
> > select p.id as p_id, pt.id as pt_id, ....
>
> Sorry, I *am* misunderstanding - I missed the "arbitrary" bit. Too late
> at night to be working...
>

<g> No harm, and I did consider that as a brute force workaround, because
we are already a little brittle in that we are assuming "p" as the table
alias. So we /could/ do:

select p.id p_id, p.*, pt.id pt_id, pt.* ...etc...

Hmmm, that might be the easy way out, albeit brute. :)

Thx, kt

> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod(at)iol(dot)ie
>

--
Kenneth Tilton

*Director of Software Development*

*MCNA Dental Plans*
200 West Cypress Creek Road
Suite 500
Fort Lauderdale, FL 33309

954-730-7131 X181 (Office)
954-628-3347 (Fax)
1-800-494-6262 X181 (Toll Free)

ktilton(at)mcna(dot)net <glipari(at)mcna(dot)net> (Email)

www.mcna.net (Website)
CONFIDENTIALITY NOTICE: This electronic mail may contain information that
is privileged, confidential, and/or otherwise protected from disclosure to
anyone other than its intended recipient(s). Any dissemination or use of
this electronic mail or its contents by persons other than the intended
recipient(s) is strictly prohibited. If you have received this
communication in error, please notify the sender immediately by reply
e-mail so that we may correct our internal records. Please then delete the
original message. Thank you.