Re: [GENERAL] Run analyze on schema

On Mon, Jun 22, 2015 at 6:53 PM, Jerry Sievers <gsievers19(at)comcast(dot)net>
wrote:

> Suresh Raja <suresh(dot)rajaabc(at)gmail(dot)com> writes:
>
> > Hi All:
> >
> > Does postgresql support schema analyze. I could not find
> > analyze schema anywhere. Can we create a function to run
> > analyze and reindex on all objects in the schema. Any
> > suggestions or ideas.
>
> Yes "we" certainly can...
>
> begin;
>
> create function foo(sch text)
> returns void as
>
> $$
> declare sql text;
>
> begin
>
> for sql in
> select format('analyze verbose %s.%s', schemaname, tablename) from
> pg_tables
> where schemaname = sch
>
> loop execute sql; end loop;
>
> end
> $$ language plpgsql;
>
> select foo('public');
> select foo('pg_catalog');
>
>
> -- Enjoy!!
>
> >
> > Thanks,
> > -Suresh Raja
> >
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres(dot)consulting(at)comcast(dot)net
> p: 312.241.7800
>

Thanks Jerry!

I too your example and added exception handling into it.

Thanks