Thread: Selecting field names?

Is there a way to 'select' the names of the fields and the field types
of a table?

--
Michael J. Hall, CCA                    Intelligence Technologies Int'l
michael@inteltec.com                            http://www.inteltec.com
secure: mikehall@leo.gov

Yes, if you start psql with the -E switch (ie, psql -E -h dbserver
database) then do a \d tablename it will show you the SQL query that's used
to display the table definition.  You can then use this to do your
selects.  Here is what I get when I do the above:

QUERY: SELECT a.attnum, a.attname, t.typname, a.attlen, a.atttypmod,
a.attnotnull, a.atthasdef FROM pg_class c, pg_attribute a, pg_type t WHERE
c.relname = 'tablename'    and a.attnum > 0     and a.attrelid =
c.oid     and a.atttypid = t.oid   ORDER BY attnum

QUERY: SELECT viewname, definition FROM pg_views WHERE viewname like
'tablename'


At 12:57 PM 4/9/00, Michael Hall wrote:
>Is there a way to 'select' the names of the fields and the field types
>of a table?
>
>--
>Michael J. Hall, CCA                    Intelligence Technologies Int'l
>michael@inteltec.com                            http://www.inteltec.com
>secure: mikehall@leo.gov

Enclosed please find a sample:
cfmg_am=> select attname, typname from pg_attribute, pg_type, pg_class

cfmg_am-> where relname = 'pgt_processing'

cfmg_am-> and attrelid = pg_class.oid

cfmg_am-> and atttypid = pg_type.oid;

          attname           |  typname
----------------------------+-----------
 pga_classname              | name
 pga_processedbytransaction | int4
 pga_transactionnumber      | int4
 pga_pid                    | int4
 backendpid                 | int4
 oid                        | oid
 ctid                       | tid
 xmin                       | xid
 xmax                       | xid
 cmin                       | cid
 cmax                       | cid
 pga_nodeip                 | inet
 pga_minorstatus            | bpchar
 pga_majorstatus            | bpchar
 pga_modifiedon             | timestamp
 pga_createdon              | timestamp
(16 rows)

Regards,
Andrzej Mazurkiewicz

andrzej@mazurkiewicz.org
www.mazurkiewicz.org


> -----Original Message-----
> From:    Michael Hall [SMTP:michael@inteltec.com]
> Sent:    9 kwietnia 2000 17:58
> To:    pgsql-general@postgresql.org
> Subject:    [GENERAL] Selecting field names?
>
> Is there a way to 'select' the names of the fields and the field types
> of a table?
>
> --
> Michael J. Hall, CCA                    Intelligence Technologies Int'l
> michael@inteltec.com                            http://www.inteltec.com
> secure: mikehall@leo.gov