select column by position

This is a multi-part message in MIME format.

----=_NextPart_ST_12_50_37_Friday_September_24_2004_6647
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Is there a way to select a column in a table by its position rather than
the field name? If I understand correctly column position in a table is
fixed. I've not been able to find anything in the archives or docs to
indicate that it's possible to select by position.
=20
For example:
=20
CREATE TABLE Accessions (
accession_id INT8 PRIMARY KEY DEFAULT nextval('accession_id_seq'),
accenumb VARCHAR(255) NOT NULL,
instcode_id INT4 NOT NULL CONSTRAINT Accessions_instcode_id_fk
REFERENCES Institutions(institution_id),
accename VARCHAR(255)
);
=20
I would like to select accession_id by indicating column 1 somehow. Is
this at all possible?
=20
Cheers,
Jennifer
=20

************************************************************ *****
DISCLAIMER:

This email is from the Scottish Crop Research Institute, but the views expr=
essed by the sender are not necessarily the views of SCRI and its subsidiar=
ies. This email and any files transmitted with it are confidential to the =
intended recipient at the e-mail address to which it has been addressed. I=
t may not be disclosed or used by any other than that addressee.
If you are not the intended recipient you are requested to preserve this co=
nfidentiality and you must not use, disclose, copy, print or rely on this e=
-mail in any way. Please notify mail@scri.sari.ac.uk quoting the name of th=
e sender and delete the email from your system.

Although SCRI has taken reasonable precautions to ensure no viruses are pre=
sent in this email, neither the Institute nor the sender accepts any respon=
sibility for any viruses, and it is your responsibility to scan the email a=
nd the attachments (if any).



----=_NextPart_ST_12_50_37_Friday_September_24_2004_6647
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

hemas-microsoft-com:office:word" xmlns=3D"http://www.w3.org/TR/REC-html40">

ize=3D2>**************************************************** ***************=
**********************

ize=3D2>DISCLAIMER:

ize=3D2>This email is from the Scottish Crop Research Institute, but the vi=
ews expressed by the sender are not necessarily the views of SCRI and its s=
ubsidiaries.  This email and any files transmitted with it are confide=
ntial to the intended recipient at the e-mail address to which it has been =
addressed.  It may not be disclosed or used by any other than that add=
ressee.
If you are not the intended recipient you are requested to prese=
rve this confidentiality and you must not use, disclose, copy, print or rel=
y on this e-mail in any way. Please notify ..ac.uk">mail@scri.sari.ac.uk quoting the name of the sender and delete =
the email from your system.

ize=3D2>Although SCRI has taken reasonable precautions to ensure no viruses=
are present in this email, neither the Institute nor the sender accepts an=
y responsibility for any viruses, and it is your responsibility to scan the=
email and the attachments (if any).

Re: select column by position

On Fri, 24 Sep 2004, Jennifer Lee wrote:
> Is there a way to select a column in a table by its position rather than
> the field name? If I understand correctly column position in a table is
> fixed. I've not been able to find anything in the archives or docs to
> indicate that it's possible to select by position.

The general response will be that you don't really want to do this.
You should figure out some other way to query the database that doesn't
rely on column position. For example, dropping and adding columns
with ALTER TABLE command could change their positions.

If you still want to go about it, I suppose it might be possible to
write a PL/pgSQL stored procedure that determines the column name
from the system catalogs, then uses EXECUTE to run the query with
the real column name substituted in the query. See Executing
Dynamic Commands in the documentation:

http://www.postgresql.org/docs/7.4/interactive/plpgsql-state ments.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Documentation about what is stored in the system catalogs can be found:
http://www.postgresql.org/docs/7.4/interactive/catalogs.html



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html