Sweet !!!
"Martha Cecilia Ayala "Hern�ndez"" <cecilia_ayala@sadm.com.mx> 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
>
