Thread: C++: headache with PgDatabase::GetValue (int, string)

Hi, 

I wonder if this is an oversight in the design of the PgDatabase 
class, or if I'm missing something, or doing something wrong.

Say I have two unrelated tables with fields witht the same name 
(e.g., table Employees, with a field Name, and table Departments, 
also with a field Name).  I have a SELECT statement that returns 
data from both tables, and I need both Name fields: 
   "select Emp.Name, Dept.Name from Emp, Dept where ....... "

Now, I (obviously) want to use GetValue (row, "field_name") to 
get the values. 

If I try GetValue (row, "Name")  I'm in trouble (it won't know 
which one -- and notice that it's not a case of an inner join, 
where the Name will happen to be the same), and if I do 
GetValue (row, "Emp.Name"), it returns NULL -- and the name 
lookup logs an message to stderr. 

Is there a workaround?  (excluding of course the rather infamous 
possibility of using GetValue(int,int) directly...  I have already 
enough trouble with my natural bugs to be asking for more trouble 
:-)) 

Thanks! 

Carlos
--

"Carlos Moreno" <moreno@mochima.com> writes:
> I have a SELECT statement that returns 
> data from both tables, and I need both Name fields: 

>     "select Emp.Name, Dept.Name from Emp, Dept where ....... "

> Now, I (obviously) want to use GetValue (row, "field_name") to 
> get the values. 

If you "obviously" want to use a fetch-by-name operation to fetch
fields, then it'd behoove you to name the columns differently in the
first place.  For example:
   select Emp.Name as emp_name, Dept.Name as dep_name from ...
        regards, tom lane

Carlos Moreno writes:

> Say I have two unrelated tables with fields witht the same name
> (e.g., table Employees, with a field Name, and table Departments,
> also with a field Name).  I have a SELECT statement that returns
> data from both tables, and I need both Name fields:
>
>     "select Emp.Name, Dept.Name from Emp, Dept where ....... "
>
> Now, I (obviously) want to use GetValue (row, "field_name") to
> get the values.

You must use AS clauses to give the two fields different output names.

-- 
Peter Eisentraut   peter_e@gmx.net

>>     "select Emp.Name, Dept.Name from Emp, Dept where ....... "
>
>> Now, I (obviously) want to use GetValue (row, "field_name") to 
>> get the values. 
>
>If you "obviously" want to use a fetch-by-name operation to fetch
>fields, then it'd behoove you to name the columns differently in the
>first place.  For example:
>
>    select Emp.Name as emp_name, Dept.Name as dep_name from ...

A-ha!!  The archi-known old trick of an alias for the column names!!
:-)  (yeah, ok, so I'm watching too much "Get Smart" lately  :-))

That also solves the second part of my problem (which I forgot to 
mention when I wrote the message), which is when you select an 
expression, such as "SELECT lower(name) .....". 

I still was expecting the GetValue (row, "table.field") idiom to 
work  (I believe it works with the MySQL client API -- I know, I 
know, who cares about that toy  :-)), but this solution is good 
anyway (I mean, it seems like a good idea to do it in the first 
place), and it is universal, so I guess I'll stick to it.

Thanks Tom and Peter for your replies! 

Carlos
--