From RbrtBrn3@aol.com Sun Apr 8 03:12:01 2001 Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f383C1858498 for ; Sat, 7 Apr 2001 23:12:01 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from imo-m04.mx.aol.com (imo-m04.mx.aol.com [64.12.136.7]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f32M1JK53827 for ; Mon, 2 Apr 2001 18:01:19 -0400 (EDT) (envelope-from RbrtBrn3@aol.com) Received: from RbrtBrn3@aol.com by imo-m04.mx.aol.com (mail_out_v29.14.) id k.1e.138d30e8 (3992) for ; Mon, 2 Apr 2001 18:01:05 -0400 (EDT) From: RbrtBrn3@aol.com Message-ID: <1e.138d30e8.27fa50a1@aol.com> Date: Mon, 2 Apr 2001 18:01:05 EDT Subject: Can anyone explain how this works? To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="part1_1e.138d30e8.27fa50a1_boundary" Content-Disposition: Inline X-Mailer: AOL 6.0 for Windows UK sub 58 X-Archive-Number: 200104/106 X-Sequence-Number: 2452 --part1_1e.138d30e8.27fa50a1_boundary Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hi, I recently posted this same question a few weeks back but lost the reply someone kindly sent. The question again how exactly does this query work: it will return all attributes and respective data types of a given table': select attname, typname from pg_class c, pg_attribute a, pg_type t where relname = relation_name and attrelid = c.oid and atttypid = t.oid and attnum > 0 order by attnum; Many thanks, Rob Burne. --part1_1e.138d30e8.27fa50a1_boundary Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hi,

I recently posted this same question a few weeks back but lost the reply
someone kindly sent. The question again how exactly does this query work:

it will return all attributes and respective data types of a given table':

select attname, typname
from pg_class c, pg_attribute a, pg_type t
where relname = relation_name and
attrelid = c.oid and
atttypid = t.oid and
attnum > 0
order by attnum;

Many thanks,

Rob Burne.
--part1_1e.138d30e8.27fa50a1_boundary-- From jburton@scw.org Sun Apr 8 09:51:00 2001 Received: from olympus.scw.org (209-9-156-98.sdsl.cais.net [209.9.156.98]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f389od864428 for ; Sun, 8 Apr 2001 05:51:00 -0400 (EDT) (envelope-from jburton@scw.org) Received: from localhost (joel@localhost) by olympus.scw.org (8.10.1/8.10.1) with ESMTP id f389oQl19985; Sun, 8 Apr 2001 05:50:26 -0400 X-Authentication-Warning: olympus.scw.org: joel owned process doing -bs Date: Sun, 8 Apr 2001 05:50:26 -0400 (EDT) From: Joel Burton X-Sender: joel@olympus.scw.org To: RbrtBrn3@aol.com cc: pgsql-sql@postgresql.org Subject: Re: Can anyone explain how this works? In-Reply-To: <1e.138d30e8.27fa50a1@aol.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200104/109 X-Sequence-Number: 2455 On Mon, 2 Apr 2001 RbrtBrn3@aol.com wrote: > Hi, > > I recently posted this same question a few weeks back but lost the reply > someone kindly sent. The question again how exactly does this query work: > > it will return all attributes and respective data types of a given table': > > select attname, typname > from pg_class c, pg_attribute a, pg_type t > where relname = relation_name and > attrelid = c.oid and > atttypid = t.oid and > attnum > 0 > order by attnum; Understanding a few minutes' worth of the system tables hold will help a lot here--you can find that in the Developer's Guide. Eseentially, pg_class hold "classes" (ie tables, views, sequences, etc.) pg_attribute holds "Attributes" (ie fields). This query joins togetehr pg_class and pg_Attribute, showing you all attributes for a class with name = 'relation name'. attnum > 0 is perhaps the only odd part -- it has to do w/hiding certain system columns of tables that ordinary users don't realize are there are don't care about. -- Joel Burton Director of Information Systems, Support Center of Washington