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@xxxxxxxxx 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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
