pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
I have elaborated some useful views...
2+ messages / 2 participants
[nested] [flat]

* I have elaborated some useful views...
@ 2005-03-01 22:57 Martha Cecilia Ayala Hernández <cecilia_ayala@sadm.com.mx>
  2005-03-02 16:55 ` Re: I have elaborated some useful views... Greg Patnude <gpatnude@hotmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Martha Cecilia Ayala Hernández @ 2005-03-01 22:57 UTC (permalink / raw)
  To: pgsql-admin

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.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: I have elaborated some useful views...
  2005-03-01 22:57 I have elaborated some useful views... Martha Cecilia Ayala Hernández <cecilia_ayala@sadm.com.mx>
@ 2005-03-02 16:55 ` Greg Patnude <gpatnude@hotmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Greg Patnude @ 2005-03-02 16:55 UTC (permalink / raw)
  To: pgsql-admin

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
> 





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-03-02 16:55 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-01 22:57 I have elaborated some useful views... Martha Cecilia Ayala Hernández <cecilia_ayala@sadm.com.mx>
2005-03-02 16:55 ` Greg Patnude <gpatnude@hotmail.com>

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