Thread: column number instead of column name

how can i make use of the column number instead of the column name when making the select statement?

for instance:
"SELECT mytable.col[1], mytable.col[2], mytable.col[3], mytable.userid from mytable"

i have several tables and i need to extract the first three columns and a known column. the first three columns for the
differenttables vary, but all the tables have the userid column. 


_____________________________________________________________
Check out Atenista.Net (www.atenista.net)- new design, regular content and additional services!

On Fri, Jan 20, 2006 at 12:24:37AM -0800, First Last wrote:
> how can i make use of the column number instead of the column
> name when making the select statement?
>
> for instance:
> "SELECT mytable.col[1], mytable.col[2], mytable.col[3], mytable.userid from mytable"

I don't think you can do that in ordinary SQL, but you could query
the system catalogs to get the table structure and generate a list
of column names from that.

> i have several tables and i need to extract the first three columns
> and a known column. the first three columns for the different tables
> vary, but all the tables have the userid column.

Can you tell us a little more about what you're doing?  Some people
would question this design, arguing that a relation's attributes
should be referenced by name and that the order in which they happen
to be stored should be irrelevant and not relied upon.  However,
without knowing what you're doing it's hard to insist that such a
generalization should apply to your case.

--
Michael Fuhr

From:   Michael Fuhr <mike@fuhr.org>
To:   First Last <y2kdis@atenista.net>
Cc:   pgsql-novice@postgresql.org
Subject:   Re: column number instead of column name
Date:   Sat 01/21/06 10:20 PM


On Fri, Jan 20, 2006 at 12:24:37AM -0800, First Last wrote:
> how can i make use of the column number instead of the column
> name when making the select statement?
>
> for instance:
> "SELECT mytable.col[1], mytable.col[2], mytable.col[3], mytable.userid
 from mytable"

I don't think you can do that in ordinary SQL, but you could query
the system catalogs to get the table structure and generate a list
of column names from that.

> i have several tables and i need to extract the first three columns
> and a known column. the first three columns for the different tables
> vary, but all the tables have the userid column.

Can you tell us a little more about what you're doing?  Some people
would question this design, arguing that a relation's attributes
should be referenced by name and that the order in which they happen
to be stored should be irrelevant and not relied upon.  However,
without knowing what you're doing it's hard to insist that such a
generalization should apply to your case.

--
Michael Fuhr

------------------------------------------------------------------

Actually the order by which the columns were made represents the relevance of the relationship of the column to the
tableit belongs (i.e., information in a particular column is more important than the succeeding column). The various
tableshowever differ in the number of level of information they present (i.e., they vary in number of columns) so that
whenevera user makes a query, i just want to limit the level of information that will be presented to the first three
columns.Furthermore, the names of the columns across the tables are not the same making a single query for all tables
notapplicable. There is a lone column though whose name is common to all the tables, but the position of this column
couldbe anywhere from fourth to last. I also want this particular column in addition to the first three columns. 

I know the database has a bad design, but it's what i have and i'm not supposed to revise it.

p.s.
I hope the explanation is not a bad one. I'm not so good in making one. Also, it's my first time to post in response to
areply so just correct me if my method is not correct. Thank you. 








_____________________________________________________________
Check out Atenista.Net (www.atenista.net)- new design, regular content and additional services!