1

In a Postgres libpq sql there is a function PQfnumber: Returns the column number associated with the given column name.

Lets say I have a select: 

select a.*, b.* from a, b where a.id = b.id

now if I will call 

number = PQfnumber(pgresult, "a.id");

it will return -1.

Correct way is to call: 

number = PQfnumber(pgresult, "id");

which returns position of a.id. So how would I need to call the function to get column number of b.id? The only way around it seems to write a different select:

select a.id as a_id, a.*, b.id as b_id, b.* from a, b where a.id = b.id

number = PQfnumber(pgresult, "b_id");

Any other way around this?

Tadzys
  • 1,044
  • 3
  • 16
  • 22

2 Answers2

1

No, you've found the right way.

Of course, with a.id = b.id in an inner join (as in the example code), why would you care which column you were looking at? Also, there are good reasons not to have just an id column as the primary key of every table. Even if a lot of tables have single-column integer keys, if you consistently name columns which hold a primary key to a given table, terser and more efficient syntax like JOIN ... USING is available.

kgrittn
  • 18,113
  • 3
  • 39
  • 47
0

If you use construct like this:

number = PQfnumber(pgresult, "a.id");

then you're query should contain a column alias like this:

SELECT a.id AS "a.id", b.* FROM a, b WHERE a.id = b.id;

You do have ambiguity in your code, should you tried such query in the PL/pgSQL language, you would have received the 42702: ambiguous_column exception.

I see the only way out here — you should give unique aliases for all the ambitious columns of your query. In fact, it is a good practice to give aliases for all columns, I always do so.

vyegorov
  • 21,787
  • 7
  • 59
  • 73