Re: SELECT with WHERE clause by column number

On mán, 2006-12-18 at 09:17 -0800, Carlos Santos wrote:

>
> SELECT id FROM myTable WHERE column-number = 'value';
> (PS: The id column is the primary key of myTable).
>
> That is a select using column number in the WHERE clause what don't
> exists in SQL.
>
> I need this because there's a situation in my program where I don't
> have the column name.
> I've solved that querying Postgresql the name of the column with that
> number, and then creating the SELECT query.
> But this solution is slow... two database calls...
>
> Can I do this with a single query or in a faster way through SQL, an
> internal function or through a Procedural Language?

this can be done with the more dynamic prodedural languages.

this should be possible too with pl/pgsql using your first query
+ EXECUTE
this is still 2 queries, but only one client round-trip.

this can also be done in one SQL query with some creative
misuse of an UNION ALL within a subquery, if you know the
number of columns in your table.

gnari