Em 14 de novembro de 2014 11:18, Paulo <[email protected]> escreveu:
> Olá Pessoal, > > > > Preciso criar uma trigger para todas as tabelas. > > Executo a sentença, mas não grava nas tabelas. > > > > SELECT 'CREATE TRIGGER minha_trigger AFTER INSERT OR UPDATE OR DELETE ON > '||tablename||' FOR EACH ROW EXECUTE PROCEDURE funcao_minha_trigger();' > > FROM pg_tables WHERE schemaname = 'public'; > > > > Se copio o retorno da sentença acima e executo manualmente ele grava > normalmente. > > > > CREATE TRIGGER minha_trigger AFTER INSERT OR UPDATE OR DELETE ON tabela > FOR EACH ROW EXECUTE PROCEDURE funcao_minha_trigger(); > > > > > > Alguém poderia me mostrar o problema ? > > > > Obrigado. > > > > Paulo. > > PostgreSQL 9.1 > > > > > > > > > > _______________________________________________ > pgbr-geral mailing list > [email protected] > https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral > > Paulo, esse comando de cima somente gera o select para vc executar manualmente. caso queira que esse retorno ja execute utilize o exemplo abaixo BEGIN TRANSACTION; DO $$DECLARE r record; DECLARE s TEXT; BEGIN FOR r IN select c.table_schema,c. table_name from information_schema.tables t inner join information_schema.columns c on c.table_catalog = t.table_catalog and c.table_schema = t.table_schema and c.table_name = t.table_name left join information_schema.key_column_usage u on c.table_catalog = u.table_catalog and c.table_schema = u.table_schema and c.table_name = u.table_name and c.column_name = u.column_name where t.table_type='VIEW' and c.table_schema not like '%pg%' and c.table_schema ='dah' C.COLUMN_NAME like 'hash%' group by 1,2 LOOP s := 'DROP VIEW ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ';'; EXECUTE s; RAISE NOTICE 's = % ',s; END LOOP; END$$; ROLLBACK TRANSACTION; -- Douglas Fabiano Specht
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
