X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0CAA054F47 for ; Tue, 1 Mar 2005 23:17:03 +0000 (GMT) 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 28441-08 for ; Tue, 1 Mar 2005 23:17:02 +0000 (GMT) Received: from aragorn.sadm.com.mx (dsl-200-67-193-252.prod-empresarial.com.mx [200.67.193.252]) by svr1.postgresql.org (Postfix) with ESMTP id 8E29454CF8 for ; Tue, 1 Mar 2005 23:16:59 +0000 (GMT) Received: from aragorn.sadm.com.mx (aragorn.sadm.com.mx [127.0.0.1]) by aragorn.sadm.com.mx (8.12.8/8.12.8) with ESMTP id j21Mv9SJ018031 for ; Tue, 1 Mar 2005 16:57:09 -0600 Received: (from apache@localhost) by aragorn.sadm.com.mx (8.12.8/8.12.8/Submit) id j21Mv9Xt018029; Tue, 1 Mar 2005 16:57:09 -0600 X-Authentication-Warning: aragorn.sadm.com.mx: apache set sender to cecilia_ayala@localhost using -f Received: from 192.168.1.21 (SquirrelMail authenticated user cecilia_ayala); by aragorn with HTTP; Tue, 1 Mar 2005 16:57:09 -0600 (CST) Message-ID: <38509.192.168.1.21.1109717829.squirrel@192.168.1.21> Date: Tue, 1 Mar 2005 16:57:09 -0600 (CST) Subject: I have elaborated some useful views... From: Martha Cecilia Ayala =?iso-8859-1?Q?Hern=E1ndez?= To: pgsql-admin@postgresql.org User-Agent: SquirrelMail/1.4.3a-0.f0.9.1.legacy X-Mailer: SquirrelMail/1.4.3a-0.f0.9.1.legacy MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/12 X-Sequence-Number: 16510 Hello: I have elaborated some useful views for administration purposes: create or replace view pga_functions as select l.lanname as language, n.nspname || '.' || p.proname || '(' || pg_catalog.oidvectortypes(p.proargtypes) || ')' as name, t.typname as returntype, '\n\n'|| 'CREATE OR REPLACE FUNCTION ' || n.nspname ||'.'|| p.proname ||'('|| pg_catalog.oidvectortypes(p.proargtypes) ||')\n'|| ' RETURNS '||t.typname||' AS'|| '\n''\n' || (select case when lanname <> 'c' then replace(prosrc,'\'','\\\'') else replace(prosrc,'\'','\\\'') ||'.so' end) || '\n''\n' || ' LANGUAGE ''' || l.lanname || ''' VOLATILE;\n' as source from pg_proc p, pg_type t, pg_namespace n, pg_language l where p.prorettype = t.oid and p.pronamespace = n.oid and p.prolang = l.oid; create or replace view pga_columns as select pg_class.relname as tablename, pg_attribute.attname as columnname from pg_class, pg_attribute where pg_class.oid = pg_attribute.attrelid and pg_attribute.attnum >= 1 and pg_class.relkind = 'r' order by pg_class.relname, pg_attribute.attnum; create or replace view pga_objects as select pg_user.usename as owner, pg_class.relname as objectname, CASE pg_class.relkind WHEN 'r' THEN 't' WHEN 'S' THEN 's' WHEN 'i' THEN 'i' WHEN 'v' THEN 'v' END as objecttype, pg_class.relpages*8 as sizek from pg_class, pg_user where pg_class.relowner = pg_user.usesysid and pg_class.relkind not in ('t','s') order by pg_user.usename, pg_class.relkind, pg_class.relname; I hope it can help other people Martha Cecilia Ayala Hernández Strategic Analysis de México, S.A.