From: David Johnston <polobo@yahoo.com>
To: johnf@jfcomputer.com
To: pgsql-sql@postgresql.org
Subject: Re: using ordinal_position
Date: Thu, 7 Jun 2012 19:32:52 -0400
Message-ID: <00c601cd4505$dcbab8f0$96302ad0$@yahoo.com> (raw)
In-Reply-To: <4FD1368E.5000304@jfcomputer.com>
References: <4FD1368E.5000304@jfcomputer.com>
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of John Fabiani
> Sent: Thursday, June 07, 2012 7:18 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] using ordinal_position
>
> I'm attempting to retrieve data using a select statement without knowing
the
> column names. I know the ordinal position but not the name of the column
> (happens to be a date::text and I have 13 fields).
>
> Below provides the name of the column in position 3:
>
> select column_name from (select column_name::text, ordinal_position from
> information_schema.columns where
> table_name='wk_test') as foo where ordinal_position = 3;
>
> But how can I use the above as a column name in a normal select statement.
>
> Unlike other databases I just can't use ordinal position in the select
> statement - RIGHT???
>
> Johnf
>
This seems like a seriously messed up requirement but I guess the easiest
way would be as follows:
SELECT tbl.col3 FROM (SELECT * FROM table) tbl (col1, col2, col3)
Basically you select ALL columns (thus not caring about their names and
always getting the defined order) from the table and give explicit aliases
to columns 1 though N where N is the desired column position you want to
return. All subsequent columns will retain their original names. If the
parent query you can then simply select the column alias you assigned to the
desired column position.
If you query the catalog for the true column name you would have to use
pl/pgSQL and EXECUTE to run the query against a manually built (stringified)
query; SQL proper does not allow for table and column names to be variable.
That said you may find it worthwhile to publish the WHY behind your inquiry
to see if some other less cumbersome and error-prone solution can be found.
While column order is fairly static it is not absolute and if the column
order were to change you would have no way of knowing. At least when using
actual column names the query would fail with an unknown column name
exception instead of possibly silently returning bad data. The extra layer
of indirection just seems dirty to me - but aside from the possibility of
column order changes I don't see any major downsides. Since you are just
dealing with column aliases it should not meaningfully impact query plan
generation and thus it should be no slower than a more direct query.
David J.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, johnf@jfcomputer.com
Subject: Re: using ordinal_position
In-Reply-To: <00c601cd4505$dcbab8f0$96302ad0$@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox