From RbrtBrn3@aol.com Tue Mar 13 01:46:55 2001 Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f2D1ktx17013 for ; Mon, 12 Mar 2001 20:46:55 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from imo-r19.mx.aol.com (imo-r19.mx.aol.com [152.163.225.73]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f29FTYx71046 for ; Fri, 9 Mar 2001 10:29:34 -0500 (EST) (envelope-from RbrtBrn3@aol.com) Received: from RbrtBrn3@aol.com by imo-r19.mx.aol.com (mail_out_v29.5.) id k.28.1237f87b (3847) for ; Fri, 9 Mar 2001 10:29:02 -0500 (EST) From: RbrtBrn3@aol.com Message-ID: <28.1237f87b.27da50bd@aol.com> Date: Fri, 9 Mar 2001 10:29:01 EST Subject: How does this query work.....? To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="part1_28.1237f87b.27da50bd_boundary" Content-Disposition: Inline X-Mailer: AOL 6.0 for Windows UK sub 58 X-Archive-Number: 200103/239 X-Sequence-Number: 1898 --part1_28.1237f87b.27da50bd_boundary Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hi, I am using the following query to find the attributes of a given table, and their datatypes: select typname,attname from pg_class c, pg_attribute a,pg_type t where relname = 'table_name' and attrelid = c.oid and atttypid = t.oid and attnum > 0; Can anybody explain how this query actually works - I cannot figure it. Thanks in advance. Rob Burne. --part1_28.1237f87b.27da50bd_boundary Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hi,

I am using the following query to find the attributes of a given table, and
their datatypes:

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

Can anybody explain how this query actually works - I cannot figure it.
Thanks in advance.

Rob Burne.
--part1_28.1237f87b.27da50bd_boundary-- From sszabo@megazone23.bigpanda.com Tue Mar 13 02:41:15 2001 Received: from megazone23.bigpanda.com (rfx-64-6-210-138.users.reflexcom.com [64.6.210.138]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2D2fFx26556 for ; Mon, 12 Mar 2001 21:41:15 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: from localhost (sszabo@localhost) by megazone23.bigpanda.com (8.11.2/8.11.2) with ESMTP id f2D2f5D98970; Mon, 12 Mar 2001 18:41:06 -0800 (PST) Date: Mon, 12 Mar 2001 18:41:05 -0800 (PST) From: Stephan Szabo To: RbrtBrn3@aol.com cc: pgsql-sql@postgresql.org Subject: Re: How does this query work.....? In-Reply-To: <28.1237f87b.27da50bd@aol.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200103/245 X-Sequence-Number: 1904 pg_class holds the relation information (tables, etc) pg_attribute holds attribute information (attname), it keeps the oid of the relation it's on in attrelid and the oid of the type as atttypid pg_type holds type information (typname) The attnum>0 is to limit the check to user attributes. There are additional attributes (oid, xmin, etc...) defined on the tables that have attnum<0 and you usually don't care about that. On Fri, 9 Mar 2001 RbrtBrn3@aol.com wrote: > Hi, > > I am using the following query to find the attributes of a given table, and > their datatypes: > > select typname,attname > from pg_class c, pg_attribute a,pg_type t > where relname = 'table_name' and > attrelid = c.oid and > atttypid = t.oid and > attnum > 0; > > Can anybody explain how this query actually works - I cannot figure it. > Thanks in advance. > > Rob Burne. >