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 2704DD7C97 for ; Mon, 19 Sep 2005 20:11:12 -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 44177-04 for ; Mon, 19 Sep 2005 23:11:04 +0000 (GMT) Received: from intranet.squiz.net (extranet.squiz.net [203.222.154.144]) by svr1.postgresql.org (Postfix) with ESMTP id B6F69D71EA for ; Mon, 19 Sep 2005 20:11:04 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by intranet.squiz.net (Postfix) with ESMTP id 633594071C; Tue, 20 Sep 2005 09:09:14 +1000 (EST) Received: from intranet.squiz.net ([127.0.0.1]) by localhost (intranet [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16401-08; Tue, 20 Sep 2005 09:09:14 +1000 (EST) Received: from [192.168.0.116] (unknown [192.168.0.116]) by intranet.squiz.net (Postfix) with ESMTP id 3CA7D2E9E1 for ; Tue, 20 Sep 2005 09:09:14 +1000 (EST) Message-ID: <432F450F.4080700@squiz.net> Date: Tue, 20 Sep 2005 09:09:03 +1000 From: Marc McIntyre User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-php@postgresql.org Subject: Re: Getting list of Indexes & contrains References: <200509082328.40506.andreil1@starlett.lv> In-Reply-To: <200509082328.40506.andreil1@starlett.lv> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at squiz.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 required=5 tests=[none] X-Spam-Level: X-Archive-Number: 200509/12 X-Sequence-Number: 3252 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.