pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
catalog of postgres
8+ messages / 5 participants
[nested] [flat]

* catalog of postgres
@ 2005-04-27 18:02 Mario Soto Cordones <msotocl@gmail.com>
  2005-04-27 18:09 ` Re: catalog of postgres Alvaro Herrera <alvherre@dcc.uchile.cl>
  0 siblings, 1 reply; 8+ messages in thread

From: Mario Soto Cordones @ 2005-04-27 18:02 UTC (permalink / raw)
  To: pgsql-admin

Hi guys
 
i have a following query:

SELECT nspname as schema, relname as objeto
FROM
  pg_class bc,
  pg_attribute ta,
  pg_namespace ns,
  pg_type ty
WHERE
  ta.attrelid = bc.oid
  and ta.attnum > 0
  and not ta.attisdropped
  and nspname <> 'information_schema' and nspname not like 'pg_%'
  and relam = 0
  and bc.relnamespace = ns.oid
  and bc.relname not like 'pg_%'
  and ta.atttypid = ty.oid
  group by nspname, relname
  order by nspname, relname asc


with this query I obtain the schema name and the objects of this it , but
 like I can know that they are, that is to say if they are tables,
views, functions, sequences, etc ????????

thank  for all

-- 
cordialmente,

Ing. Mario Soto Cordones



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

* Re: catalog of postgres
  2005-04-27 18:02 catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
@ 2005-04-27 18:09 ` Alvaro Herrera <alvherre@dcc.uchile.cl>
  2005-04-27 18:14   ` Re: catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Alvaro Herrera @ 2005-04-27 18:09 UTC (permalink / raw)
  To: Mario Soto Cordones <msotocl@gmail.com>; +Cc: pgsql-admin

On Wed, Apr 27, 2005 at 02:02:09PM -0400, Mario Soto Cordones wrote:

> with this query I obtain the schema name and the objects of this it , but
>  like I can know that they are, that is to say if they are tables,
> views, functions, sequences, etc ????????

See pg_class.relkind.

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"El día que dejes de cambiar dejarás de vivir"



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

* Re: catalog of postgres
  2005-04-27 18:02 catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
  2005-04-27 18:09 ` Re: catalog of postgres Alvaro Herrera <alvherre@dcc.uchile.cl>
@ 2005-04-27 18:14   ` Mario Soto Cordones <msotocl@gmail.com>
  2005-04-27 18:29     ` Re: catalog of postgres Tom Lane <tgl@sss.pgh.pa.us>
  2005-04-27 18:35     ` Re: catalog of postgres Lyubomir Petrov <lpetrov@sysmaster.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Mario Soto Cordones @ 2005-04-27 18:14 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@dcc.uchile.cl>; +Cc: pgsql-admin

OK but views and tables for example have the same one relkind 

thank



2005/4/27, Alvaro Herrera <alvherre@dcc.uchile.cl>:
> On Wed, Apr 27, 2005 at 02:02:09PM -0400, Mario Soto Cordones wrote:
> 
> > with this query I obtain the schema name and the objects of this it , but
> >  like I can know that they are, that is to say if they are tables,
> > views, functions, sequences, etc ????????
> 
> See pg_class.relkind.
> 
> --
> Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
> "El día que dejes de cambiar dejarás de vivir"
> 


-- 
cordialmente,

Ing. Mario Soto Cordones



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

* Re: catalog of postgres
  2005-04-27 18:02 catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
  2005-04-27 18:09 ` Re: catalog of postgres Alvaro Herrera <alvherre@dcc.uchile.cl>
  2005-04-27 18:14   ` Re: catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
@ 2005-04-27 18:29     ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-04-27 19:57       ` Re: catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Tom Lane @ 2005-04-27 18:29 UTC (permalink / raw)
  To: Mario Soto Cordones <msotocl@gmail.com>; +Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>; pgsql-admin

Mario Soto Cordones <msotocl@gmail.com> writes:
> OK but views and tables for example have the same one relkind 

Not for many years now (certainly not in any release that has pg_namespace).

			regards, tom lane



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

* Re: catalog of postgres
  2005-04-27 18:02 catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
  2005-04-27 18:09 ` Re: catalog of postgres Alvaro Herrera <alvherre@dcc.uchile.cl>
  2005-04-27 18:14   ` Re: catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
  2005-04-27 18:29     ` Re: catalog of postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-04-27 19:57       ` Mario Soto Cordones <msotocl@gmail.com>
  2005-04-27 20:41         ` Re: catalog of postgres Alvaro Herrera <alvherre@dcc.uchile.cl>
  0 siblings, 1 reply; 8+ messages in thread

From: Mario Soto Cordones @ 2005-04-27 19:57 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>; pgsql-admin

OK  i have a big mistake, i saw bad, now my new query is

SELECT nspname as esquema, relname as objeto,
(CASE relkind
  WHEN 'v' THEN 'Vista'
  WHEN 'i' THEN 'Indice'
  WHEN 'S' THEN 'Secuencia'
  WHEN 'r' THEN 'Tabla'
  WHEN 'c' THEN 'Tipo'
  END) as tipo
FROM
  pg_class bc,
  pg_attribute ta,
  pg_namespace ns,
  pg_type ty
WHERE
  ta.attrelid = bc.oid
  and ta.attnum > 0
  and not ta.attisdropped
  and nspname <> 'information_schema' and nspname not like 'pg_%'
  ----and relam = 0
  and bc.relnamespace = ns.oid
  and bc.relname not like 'pg_%'
  and ta.atttypid = ty.oid
  group by nspname, relname, relkind
  order by tipo desc
  
  select * from pg_class
  where relkind = 'i'

but not you where they stay the functions

thank you for all



2005/4/27, Tom Lane <tgl@sss.pgh.pa.us>:
> Mario Soto Cordones <msotocl@gmail.com> writes:
> > OK but views and tables for example have the same one relkind
> 
> Not for many years now (certainly not in any release that has pg_namespace).
> 
>                         regards, tom lane
> 


-- 
cordialmente,

Ing. Mario Soto Cordones



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

* Re: catalog of postgres
  2005-04-27 18:02 catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
  2005-04-27 18:09 ` Re: catalog of postgres Alvaro Herrera <alvherre@dcc.uchile.cl>
  2005-04-27 18:14   ` Re: catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
  2005-04-27 18:29     ` Re: catalog of postgres Tom Lane <tgl@sss.pgh.pa.us>
  2005-04-27 19:57       ` Re: catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
@ 2005-04-27 20:41         ` Alvaro Herrera <alvherre@dcc.uchile.cl>
  0 siblings, 0 replies; 8+ messages in thread

From: Alvaro Herrera @ 2005-04-27 20:41 UTC (permalink / raw)
  To: Mario Soto Cordones <msotocl@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin

On Wed, Apr 27, 2005 at 03:57:41PM -0400, Mario Soto Cordones wrote:

> SELECT nspname as esquema, relname as objeto,
> (CASE relkind
>   WHEN 'v' THEN 'Vista'
>   WHEN 'i' THEN 'Indice'
>   WHEN 'S' THEN 'Secuencia'
>   WHEN 'r' THEN 'Tabla'
>   WHEN 'c' THEN 'Tipo'
>   END) as tipo
> FROM
>   pg_class bc,
>   pg_attribute ta,
>   pg_namespace ns,
>   pg_type ty
> WHERE
>   ta.attrelid = bc.oid
>   and ta.attnum > 0
>   and not ta.attisdropped
>   and nspname <> 'information_schema' and nspname not like 'pg_%'
>   ----and relam = 0
>   and bc.relnamespace = ns.oid
>   and bc.relname not like 'pg_%'
>   and ta.atttypid = ty.oid
>   group by nspname, relname, relkind
>   order by tipo desc

Why are you using a group by here?  It's unnecesary.

> but not you where they stay the functions

See the pg_proc catalog.

I wonder if I have pointed you to the documentation before?  See the
"system catalogs" section in the internals chapter.  Also, maybe you
should take a look at whether the information_schema gives you what you
want.

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
Y una voz del caos me habló y me dijo
"Sonríe y sé feliz, podría ser peor".
Y sonreí. Y fui feliz.
Y fue peor.



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

* Re: catalog of postgres
  2005-04-27 18:02 catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
  2005-04-27 18:09 ` Re: catalog of postgres Alvaro Herrera <alvherre@dcc.uchile.cl>
  2005-04-27 18:14   ` Re: catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
@ 2005-04-27 18:35     ` Lyubomir Petrov <lpetrov@sysmaster.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Lyubomir Petrov @ 2005-04-27 18:35 UTC (permalink / raw)
  To: Mario Soto Cordones <msotocl@gmail.com>; +Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>; pgsql-admin

Yeah, same here...  Here is a test case where tables and views show up 
with the same relkind... :)


$ psql test -c "create table table1(id int); create view view1 as select 
* from table1;"
CREATE VIEW
$ psql test -c "select relname, relkind from pg_class where relname in 
('table1', 'view1');" | sed -e 's/r$/v/'
 relname | relkind
---------+---------
 table1  | r
 view1   | r
(2 rows)


But I believe this is a feature, not a bug. Look at this, it is 
explained here 
http://www.postgresql.org/docs/8.0/static/catalog-pg-class.html.

Regards,
Lyubomir Petrov



Mario Soto Cordones wrote:

>OK but views and tables for example have the same one relkind 
>
>thank
>
>
>
>2005/4/27, Alvaro Herrera <alvherre@dcc.uchile.cl>:
>  
>
>>On Wed, Apr 27, 2005 at 02:02:09PM -0400, Mario Soto Cordones wrote:
>>
>>    
>>
>>>with this query I obtain the schema name and the objects of this it , but
>>> like I can know that they are, that is to say if they are tables,
>>>views, functions, sequences, etc ????????
>>>      
>>>
>>See pg_class.relkind.
>>
>>--
>>Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
>>"El día que dejes de cambiar dejarás de vivir"
>>
>>    
>>
>
>
>  
>




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

* Re: catalog of postgres
@ 2005-04-27 18:55 Goulet, Dick <DGoulet@vicr.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Goulet, Dick @ 2005-04-27 18:55 UTC (permalink / raw)
  To: Lyubomir Petrov <lpetrov@sysmaster.com>; Mario Soto Cordones <msotocl@gmail.com>; +Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>; pgsql-admin

This might help, got it from a project run by Great Bridge Software, now defunct, to create an Oracle like data dictionary for PostGreSql:

CREATE VIEW all_objects
AS
  SELECT  UPPER(pg_get_userbyid (cls.relowner)) AS owner
          ,UPPER(cls.relname) AS object_name
          ,CASE WHEN cls.relkind = 'r' THEN CAST('TABLE' AS VARCHAR(18))
                WHEN cls.relkind = 'i' THEN CAST('INDEX' AS VARCHAR(18))
                WHEN cls.relkind = 'S' THEN CAST('SEQUENCE' AS VARCHAR(18))
                WHEN cls.relkind = 's' THEN CAST('SPECIAL' AS VARCHAR(18))
                WHEN cls.relkind = 't' THEN CAST('TOAST TABLE' AS VARCHAR(18))
          END AS object_type
          ,CAST(NULL AS DATE) AS created
          ,CAST('VALID' AS VARCHAR(7)) AS status
  FROM pg_class cls
  WHERE (NOT cls.relhasrules
         AND NOT EXISTS (SELECT rul.rulename
                         FROM pg_rewrite rul
                         WHERE ((rul.ev_class = cls.oid) AND (rul.ev_type = '1'::"char"))
                         )
         )
  UNION ALL
  SELECT UPPER(pg_get_userbyid(cls.relowner)) AS owner
       , UPPER(cls.relname) AS OBJECT_NAME
       , CAST('VIEW' AS VARCHAR(18)) as object_type
       , CAST(NULL AS DATE) AS created
       , CAST('VALID' AS VARCHAR(7)) AS status
  FROM pg_class cls
  WHERE (cls.relhasrules
  AND (EXISTS (SELECT rul.rulename
               FROM pg_rewrite rul
               WHERE ((rul.ev_class = cls.oid)
               AND (rul.ev_type = '1'::"char")))))
  UNION ALL
  SELECT UPPER(pg_get_userbyid(p.proowner)) AS OWNER
       , UPPER(p.proname) AS OBJECT_NAME
       , CAST('FUNCTION' AS VARCHAR(18)) as object_type
       , CAST(NULL AS DATE) AS created
       , CAST('VALID' AS VARCHAR(7)) AS status
  FROM pg_proc p
  WHERE p.oid > 18655; 

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Lyubomir Petrov
Sent: Wednesday, April 27, 2005 2:36 PM
To: Mario Soto Cordones
Cc: Alvaro Herrera; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] catalog of postgres

Yeah, same here...  Here is a test case where tables and views show up 
with the same relkind... :)


$ psql test -c "create table table1(id int); create view view1 as select 
* from table1;"
CREATE VIEW
$ psql test -c "select relname, relkind from pg_class where relname in 
('table1', 'view1');" | sed -e 's/r$/v/'
 relname | relkind
---------+---------
 table1  | r
 view1   | r
(2 rows)


But I believe this is a feature, not a bug. Look at this, it is 
explained here 
http://www.postgresql.org/docs/8.0/static/catalog-pg-class.html.

Regards,
Lyubomir Petrov



Mario Soto Cordones wrote:

>OK but views and tables for example have the same one relkind 
>
>thank
>
>
>
>2005/4/27, Alvaro Herrera <alvherre@dcc.uchile.cl>:
>  
>
>>On Wed, Apr 27, 2005 at 02:02:09PM -0400, Mario Soto Cordones wrote:
>>
>>    
>>
>>>with this query I obtain the schema name and the objects of this it , but
>>> like I can know that they are, that is to say if they are tables,
>>>views, functions, sequences, etc ????????
>>>      
>>>
>>See pg_class.relkind.
>>
>>--
>>Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
>>"El día que dejes de cambiar dejarás de vivir"
>>
>>    
>>
>
>
>  
>


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend



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


end of thread, other threads:[~2005-04-27 20:41 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-27 18:02 catalog of postgres Mario Soto Cordones <msotocl@gmail.com>
2005-04-27 18:09 ` Alvaro Herrera <alvherre@dcc.uchile.cl>
2005-04-27 18:14   ` Mario Soto Cordones <msotocl@gmail.com>
2005-04-27 18:29     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-27 19:57       ` Mario Soto Cordones <msotocl@gmail.com>
2005-04-27 20:41         ` Alvaro Herrera <alvherre@dcc.uchile.cl>
2005-04-27 18:35     ` Lyubomir Petrov <lpetrov@sysmaster.com>
2005-04-27 18:55 Re: catalog of postgres Goulet, Dick <DGoulet@vicr.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