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 f2CJvMx54824 for ; Mon, 12 Mar 2001 14:57:22 -0500 (EST) (envelope-from pgsql-general-owner@postgresql.org) Received: from serv1.ilse.nl (serv1.ilse.nl [212.189.131.11]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f270A6x04579; Tue, 6 Mar 2001 19:10:06 -0500 (EST) (envelope-from mathijs@serv0.ilse.nl) Received: from serv0.ilse.nl (serv0.ilse.nl [212.189.131.10]) by serv1.ilse.nl (8.11.0/8.10.2) with ESMTP id f2709vE67280; Wed, 7 Mar 2001 01:09:57 +0100 (MET) Received: (from mathijs@localhost) by serv0.ilse.nl (8.11.1/8.11.1) id f2709k652768; Wed, 7 Mar 2001 01:09:46 +0100 (CET) (envelope-from mathijs) Date: Wed, 7 Mar 2001 01:09:46 +0100 From: Mathijs Brands To: Karel Zak Cc: Boulat Khakimov , pgsql-sql@postgresql.org, pgsql-general@postgresql.org Subject: Re: [SQL] Re: Re: MySQLs Describe emulator! Message-ID: <20010307010946.D51050@ilse.nl> References: <3AA44897.121D60F0@inet-interactif.com> <3764.983846777@sss.pgh.pa.us> <3AA4F0DE.D96BA904@inet-interactif.com> <20010306155522.B13152@ara.zf.jcu.cz> <3AA4FFF1.5875B665@inet-interactif.com> <20010306163732.E13152@ara.zf.jcu.cz> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <20010306163732.E13152@ara.zf.jcu.cz>; from zakkr@zf.jcu.cz on Tue, Mar 06, 2001 at 04:37:32PM +0100 X-Unexpected-Header: The Spanish Inquisition X-Archive-Number: 200103/517 X-Sequence-Number: 6007 On Tue, Mar 06, 2001 at 04:37:32PM +0100, Karel Zak allegedly wrote: > On Tue, Mar 06, 2001 at 10:19:13AM -0500, Boulat Khakimov wrote: > > > > Karel Zak wrote: > > > > On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote: > > > > Tom Lane wrote: > > > > > > > > > > Boulat Khakimov writes: > > > > > > Here is a nifty query I came up with > > > > > > that provides a detailed information on any row of any table. > > > > > > Something that is build into mySQL (DESC tablename fieldname) > > > > > > but not into PG. > > > > > > > > > > Er, what's wrong with psql's "\d table" ? > > > > > > > > 2) as a programmer I need to be able to find out as much info as > > > > possible about any given field > > > > which is what "describe" for in mySQL. > > > > > > As a programmer you can see psql source and directly found how SQL > > > query execute this tool. The PostgreSQL needn't non-standard statements > > > like MySQL's SHOW, DESC -- the postgreSQL has system catalogs. > > > > > > Karel > > > > Agreed! Why make someones life easier?? > > Let's complicate things as much as possible that way it's more > > fun,right? ;o) > > > > Dont understand how this works? No problem -- just read the source > > code. > > Dont understand how to get that to work? Not a problem -- read the > > source code! > > > > The only problem tho, the source codes tend to be thousands of lines > > when it comes > > to DBs and time is ... > > Well man, I not write this code, but I need 1 minute for found it.... > > see src/bin/psql/describe.c: > > SELECT a.attname, format_type(a.atttypid, a.atttypmod), attnotnull, > a.atthasdef, a.attnum, obj_description(a.oid) > FROM pg_class c, pg_attribute a > WHERE c.relname = 'YourTableName' AND > ^^^^^^^^^^^^^ > a.attnum > 0 AND > a.attrelid = c.oid > ORDER BY a.attnum; > > If I good remenber anywhere in PG's docs is catalog schema. It isn't > too much difficult write queries like above-mentioned, because catalog > attributes/tables names are intuitive. For start see pg_class and > pg_attribute. Karel, how about this one? It's even easier :) No need to spit through code to find this... serv0:/var/namedsrc$ psql -E -c '\d nodes' iig ********* QUERY ********* SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules FROM pg_class WHERE relname='nodes' ************************* ********* QUERY ********* SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'nodes' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum ************************* ********* QUERY ********* SELECT c2.relname FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = 'nodes' AND c.oid = i.indrelid AND i.indexrelid = c2.oid ORDER BY c2.relname ************************* Table "nodes" Attribute | Type | Modifier -----------+----------+---------- id | integer | title | text | ncount | smallint | ecount | smallint | ref | integer | moddate | integer | publish | char(1) | Indices: idx_nodes_id, idx_nodes_ref, idx_nodes_title Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum