FAQ
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

Search Discussions

  • Alvaro Herrera at Apr 27, 2005 at 6:09 pm

    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"
  • Mario Soto Cordones at Apr 27, 2005 at 6:15 pm
    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
  • Tom Lane at Apr 27, 2005 at 6:30 pm

    Mario Soto Cordones 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
  • Mario Soto Cordones at Apr 27, 2005 at 7:57 pm
    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
  • Alvaro Herrera at Apr 27, 2005 at 8:41 pm

    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.
  • Lyubomir Petrov at Apr 27, 2005 at 6:36 pm
    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"


  • Goulet, Dick at Apr 27, 2005 at 6:55 pm
    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 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-admin @
categoriespostgresql
postedApr 27, '05 at 6:02p
activeApr 27, '05 at 8:41p
posts8
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase