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 7FBBD56397 for ; Wed, 2 Mar 2005 17:08:06 +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 27886-05 for ; Wed, 2 Mar 2005 17:08:00 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 65D545516A for ; Wed, 2 Mar 2005 17:08:00 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id j22H7xtN029507 for ; Wed, 2 Mar 2005 17:08:00 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id j22Gsh9T025262 for pgsql-admin@postgresql.org; Wed, 2 Mar 2005 16:54:43 GMT (envelope-from news) From: "Greg Patnude" X-Newsgroups: pgsql.admin Subject: Re: I have elaborated some useful views... Date: Wed, 2 Mar 2005 08:55:48 -0800 Organization: dataMosaics.com Lines: 61 Message-ID: References: <38509.192.168.1.21.1109717829.squirrel@192.168.1.21> Reply-To: "Greg Patnude" X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-RFC2646: Format=Flowed; Original To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200503/31 X-Sequence-Number: 16529 Sweet !!! "Martha Cecilia Ayala "Hernández"" wrote in message news:38509.192.168.1.21.1109717829.squirrel@192.168.1.21... > 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. > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >