From: Martha Cecilia Ayala Hernández <cecilia_ayala@sadm.com.mx>
To: pgsql-admin@postgresql.org
Subject: I have elaborated some useful views...
Date: Tue, 1 Mar 2005 16:57:09 -0600 (CST)
Message-ID: <38509.192.168.1.21.1109717829.squirrel@192.168.1.21> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: cecilia_ayala@sadm.com.mx
Subject: Re: I have elaborated some useful views...
In-Reply-To: <38509.192.168.1.21.1109717829.squirrel@192.168.1.21>
References: <38509.192.168.1.21.1109717829.squirrel@192.168.1.21>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox