Thread: catalog of postgres
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
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"
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
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
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"
>>
>>
>>
>
>
>
>
This might help, got it from a project run by Great Bridge Software, now defunct, to create an Oracle like data
dictionaryfor 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
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
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.