X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9EBA3DA0BB for ; Tue, 4 Oct 2005 19:21:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 98343-08 for ; Tue, 4 Oct 2005 22:21:07 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id B534AD9B5D for ; Tue, 4 Oct 2005 19:21:08 -0300 (ADT) Received: by flake.decibel.org (Postfix, from userid 1001) id 36F231524F; Tue, 4 Oct 2005 17:21:11 -0500 (CDT) Date: Tue, 4 Oct 2005 17:21:11 -0500 From: "Jim C. Nasby" To: Marc McIntyre Cc: pgsql-php@postgresql.org Subject: Re: Getting list of Indexes & contrains Message-ID: <20051004222111.GK40138@pervasive.com> References: <200509082328.40506.andreil1@starlett.lv> <432F450F.4080700@squiz.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <432F450F.4080700@squiz.net> X-Operating-System: FreeBSD 4.11-RELEASE-p10 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 required=5 tests=[AWL=0.008] X-Spam-Level: X-Archive-Number: 200510/6 X-Sequence-Number: 3274 You might also want to look at http://pgfoundry.org/projects/newsysviews/ as some of the queries there are more efficient than what's in psql (some by a couple orders of magnitude IIRC). On Tue, Sep 20, 2005 at 09:09:03AM +1000, Marc McIntyre wrote: > If you use -E option when doing issuing a "\di" command in psql it will > show you the query that it performs to list the indexes. > > For example: > > mmcintyre@beta matrix $ psql -E -U clients marc_dev > Welcome to psql 7.4.7, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help on internal slash commands > \g or terminate with semicolon to execute query > \q to quit > > marc_dev=> \di > ********* QUERY ********** > SELECT n.nspname as "Schema", > c.relname as "Name", > CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' > THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type", > u.usename as "Owner", > c2.relname as "Table" > FROM pg_catalog.pg_class c > JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid > JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid > LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner > LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace > WHERE c.relkind IN ('i','') > AND n.nspname NOT IN ('pg_catalog', 'pg_toast') > AND pg_catalog.pg_table_is_visible(c.oid) > ORDER BY 1,2; > ************************** > List of relations > Schema | Name | Type | Owner | Table > --------+--------------------------------+-------+---------+-------------------------- > public | sq_ast_attr_name | index | clients | sq_ast_attr > public | sq_ast_attr_pkey | index | clients | sq_ast_attr > public | sq_ast_attr_type_code | index | clients | sq_ast_attr > public | sq_ast_attr_type_code_key | index | clients | sq_ast_attr > public | sq_ast_attr_uniq_val_pkey | index | clients | > sq_ast_attr_uniq_val > public | sq_ast_attr_val_assetid | index | clients | sq_ast_attr_val > public | sq_ast_attr_val_attrid | index | clients | sq_ast_attr_val > public | sq_ast_attr_val_concat | index | clients | sq_ast_attr_val > public | sq_ast_attr_val_pkey | index | clients | sq_ast_attr_val > public | sq_ast_created | index | clients | sq_ast > public | sq_ast_edit_access_pkey | index | clients | > sq_ast_edit_access > : > > You can then modify and use this query in your application. > > > Andrei Verovski (aka MacGuru) wrote: > > >Hi, > > > >I am using PostgreSQL 8 and adodb. adodb has a built-in function which > >scans and retrieves db structure as assotiative array. Unfortunately, > >it do not list indexes and constrains. What would be a SQL statement > >to retrieve them? > > > >Thanks in advance for any suggestion(s). > > > > > >************************************************ > >*** with best regards > >*** Andrei Verovski (aka MacGuru) > >*** Mac, Linux, DTP, Programming Web Site > >*** > >*** http://snow.prohosting.com/guru4mac/ > >************************************************ > > > >---------------------------(end of broadcast)--------------------------- > >TIP 6: explain analyze is your friend > > > > > > > > > -- > Marc McIntyre > MySource Matrix Lead Developer > > ..>> Sydney ...> > 92 Jarrett St T: +61 (0) 2 9568 6866 > Leichhardt, F: +61 (0) 2 9568 6733 > NSW, 2040 W: http://www.squiz.net/ > > ..>> Canberra ...> > Walter Turnbull Bldg T: +61 (0) 2 6233 0607 > 44 Sydney Ave, F: +61 (0) 2 6233 0696 > Forrest, W: http://www.squiz.net/ > ACT 2603 > > ..>> London ...> > The Old Fire Station, T: +44 (0) 20 7300 7321 > 140 Tabernacle St, F: +44 (0) 870 112 3394 > London EC2A 4SD W: http://www.squiz.co.uk/ > > .....>> Open Source - Own it - Squiz.net ...../> > > IMPORTANT: This email (and any attachments) is commercial-in-confidence and > or may be legally privileged and must not be forwarded, copied or shared > without express permission from Squiz. If you are not the intended > recipient, you may not legally copy, disclose or use the contents in any > way and you should contact squiz@squiz.net immediately and destroy this > message and any attachments. Thank you. > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461