Vlw ;)
2009/8/31 Fabrízio de Royes Mello <fabriziome...@gmail.com>
>
>
> 2009/8/31 André Pignata <andrepign...@gmail.com>
>
>>
>> CREATE OR REPLACE FUNCTION "migracao"."limpa_tabela" () RETURNS boolean AS
>> $body$
>> /* New function body */
>> declare
>> rmsg record;
>> retorno boolean;
>> begin
>> retorno := true;
>> for rmsg in (select distinct pg_tables.tablename as tabela from
>> pg_tables
>> ,pg_class
>> ,pg_namespace
>> where
>> pg_namespace.nspname = 'public'
>> and pg_tables.schemaname = pg_namespace.nspname
>> and pg_class.relname = pg_tables.tablename
>> and pg_class.relnamespace = pg_namespace.oid
>> ) loop
>> BEGIN
>> EXECUTE('delete from '||rmsg.tabela);
>> EXCEPTION
>> when integrity_constraint_violation then
>> retorno := false;
>>
>> end;
>> end loop;
>> return retorno;
>>
>> end;
>> $body$
>> LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
>>
>> <corte>
>>
>>
> André,
>
> Só uma dica, eu recomendo você utilizar o "information_schema" [1] para
> obter informações do catálogo, trocando a query...
>
> select distinct pg_tables.tablename as tabela from
> pg_tables
> ,pg_class
> ,pg_namespace
> where
> pg_namespace.nspname = 'public'
> and pg_tables.schemaname = pg_namespace.nspname
> and pg_class.relname = pg_tables.tablename
> and pg_class.relnamespace = pg_namespace.oid
>
> ...por simplesmente:
>
> select table_name as tabela
> from information_schema.tables
> where table_schema = 'public';
>
>
>
> [1] http://www.postgresql.org/docs/current/static/information-schema.html
>
> --
> Fabrízio de Royes Mello
> >> Blog sobre TI: http://fabriziomello.blogspot.com
>
> _______________________________________________
> pgbr-geral mailing list
> pgbr-geral@listas.postgresql.org.br
> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
>
>
--
André Luiz Martins Pignata
Integral Convênios Odontológicos
Gerente de TI
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
