Обсуждение: System catalog and identifying

Hi all! Didn't find any info on this, so if I missed it please guide
me to the info thx.

I know the name of the table and now I need to know what columns this
table has.

First I search system catalog named pg_class inorder to get the
postgre id of the table named relfilenode:

select relfilenode from pg_class where relname = 'table_name';

after that I search all column names from pg_attribute, like this:

select attname from pg_attribute where attrelid = relfilenode;

unfortunately, this returns correct names, but also some names that
the system uses. How can I exclude these names?

EXAMPLE:
I get the following results:

 tableoid *
 cmax *
 xmax *
 cmin *
 xmin *
 oid *
 ctid *
 sarake_id
 nimi
 kuvaus
 tyyppi
 help
 taulu_id

everything marked with * is unwanted.

Thanks beforehand!

-9902468

On 1 Aug 2002, (9902468) wrote:

> Hi all! Didn't find any info on this, so if I missed it please guide
> me to the info thx.
>
> I know the name of the table and now I need to know what columns this
> table has.
>
> First I search system catalog named pg_class inorder to get the
> postgre id of the table named relfilenode:
>
> select relfilenode from pg_class where relname = 'table_name';
>
> after that I search all column names from pg_attribute, like this:
>
> select attname from pg_attribute where attrelid = relfilenode;
>
> unfortunately, this returns correct names, but also some names that
> the system uses. How can I exclude these names?
>
> EXAMPLE:
> I get the following results:
>
>  tableoid *
>  cmax *
>  xmax *
>  cmin *
>  xmin *
>  oid *
>  ctid *
>  sarake_id
>  nimi
>  kuvaus
>  tyyppi
>  help
>  taulu_id
>
> everything marked with * is unwanted.
>


SELECT a.attname
 FROM pg_attribute a, pg_class c
 WHERE c.oid = a.attrelid
  AND c.relname = 'table_name'
  AND a.attnum > 0
 ORDER BY a.attnum

let's you do it from the table name in one query and gives you them in order.


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants