pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Getting list of Indexes & contrains
4+ messages / 4 participants
[nested] [flat]

* Getting list of Indexes & contrains
@ 2005-09-08 20:28 Andrei Verovski (aka MacGuru) <andreil1@starlett.lv>
  2005-09-13 07:58 ` Re: Getting list of Indexes & contrains Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
  2005-09-19 23:09 ` Re: Getting list of Indexes & contrains Marc McIntyre <mmcintyre@squiz.net>
  0 siblings, 2 replies; 4+ messages in thread

From: Andrei Verovski (aka MacGuru) @ 2005-09-08 20:28 UTC (permalink / raw)
  To: pgsql-php

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/
************************************************



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Getting list of Indexes & contrains
  2005-09-08 20:28 Getting list of Indexes & contrains Andrei Verovski (aka MacGuru) <andreil1@starlett.lv>
@ 2005-09-13 07:58 ` Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
  1 sibling, 0 replies; 4+ messages in thread

From: Alban Medici (NetCentrex) @ 2005-09-13 07:58 UTC (permalink / raw)
  To: pgsql-php

 If you only want list and not exact description :
Use '\di' for indexes 
And for sample description of a table '\d <yourtable>'

But for real description you have to cross pg_* tables.


Regards 
Alban 

-----Original Message-----
From: pgsql-php-owner@postgresql.org [mailto:pgsql-php-owner@postgresql.org]
On Behalf Of Andrei Verovski (aka MacGuru)
Sent: jeudi 8 septembre 2005 22:29
To: PostgreSQL - PHP
Subject: [PHP] Getting list of Indexes & contrains

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Getting list of Indexes & contrains
  2005-09-08 20:28 Getting list of Indexes & contrains Andrei Verovski (aka MacGuru) <andreil1@starlett.lv>
@ 2005-09-19 23:09 ` Marc McIntyre <mmcintyre@squiz.net>
  2005-10-04 22:21   ` Re: Getting list of Indexes & contrains Jim C. Nasby <jnasby@pervasive.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Marc McIntyre @ 2005-09-19 23:09 UTC (permalink / raw)
  To: pgsql-php

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.







^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Getting list of Indexes & contrains
  2005-09-08 20:28 Getting list of Indexes & contrains Andrei Verovski (aka MacGuru) <andreil1@starlett.lv>
  2005-09-19 23:09 ` Re: Getting list of Indexes & contrains Marc McIntyre <mmcintyre@squiz.net>
@ 2005-10-04 22:21   ` Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Jim C. Nasby @ 2005-10-04 22:21 UTC (permalink / raw)
  To: Marc McIntyre <mmcintyre@squiz.net>; +Cc: pgsql-php

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-10-04 22:21 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-08 20:28 Getting list of Indexes & contrains Andrei Verovski (aka MacGuru) <andreil1@starlett.lv>
2005-09-13 07:58 ` Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
2005-09-19 23:09 ` Marc McIntyre <mmcintyre@squiz.net>
2005-10-04 22:21   ` Jim C. Nasby <jnasby@pervasive.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox