On Fri, Jan 17, 2014 at 11:54 AM, Eloi Ribeiro <e...@openmailbox.org> wrote:
> Ola a todos, > > Estou a tentar criar uma função que crie sequencias em todas as tabelas > num determinado esquema e não consigo avançar por causa das aspas dentro de > aspas, alguém pode dar uma ajuda. > > Aqui está o código, e o problema com as aspas esta no primeiro e no > terceiro EXECUTE: > > CREATE OR REPLACE FUNCTION wosis.fun_create_sequence(sch text) > RETURNS text AS > $BODY$ > DECLARE > row record; > BEGIN > EXECUTE ' CREATE TEMP TABLE tmp AS > SELECT table_name, table_name||_id' AS id_name, 0 AS > next_value > FROM information_schema.tables > WHERE table_schema = sch > AND table_type = 'BASE TABLE' > ORDER BY table_name;'; > FOR row IN > SELECT table_name, id_name FROM tmp ORDER BY table_name > LOOP > EXECUTE 'UPDATE tmp SET next_value = (SELECT max(' || > quote_ident(row.id_name) || ')+1 FROM ' || quote_ident(sch) || '.' || > quote_ident(row.table_name) || ') WHERE table_name = ' || > quote_ident(row.table_name) || ';' > END LOOP; > FOR row IN > SELECT table_name, id_name, next_value FROM tmp ORDER BY table_name > LOOP > EXECUTE 'CREATE SEQUENCE ' || quote_ident(row.table_name) || '_' > || quote_ident(row.id_name) || '_seq START ' || quote_ident(row.next_value) > || ';' > EXECUTE 'ALTER TABLE ' || quote_ident(sch) || '.' || > quote_ident(row.table_name) || ' ALTER COLUMN ' || quote_ident(row.id_name) > || ' SET DEFAULT nextval( ' || quote_ident(row.table_name) || '_' || > quote_ident(row.id_name) || '_seq);' > END LOOP; > RETURN 'Sequences were created'; > END; > $BODY$ > LANGUAGE plpgsql VOLATILE > COST 100; > ALTER FUNCTION wosis.fun_create_sequence(text) OWNER TO postgres; > COMMENT ON FUNCTION wosis.fun_create_sequence(text) IS 'Create sequences > in all tables from a given schema'; > > É, parece que você criou uma bagunça com as aspas mesmo... Acho que vai ficar mais fácil refazer a função do que ajustar cada problema nela. Já que vamos refazer, que tal adicionar algumas dicas de ouro? ;-) Bom, primeiro, veja o modelo que criei (não testei, então pode ter algum erro que eu não tenha visto): CREATE OR REPLACE FUNCTION wosis.fun_create_sequence(sch text) RETURNS text LANGUAGE plpgsql VOLATILE AS $BODY$ DECLARE row record; BEGIN FOR row IN SELECT table_name, table_name||'_id' AS id_name, 0 AS next_value FROM information_schema.tables WHERE table_schema = sch AND table_type = 'BASE TABLE' ORDER BY table_name LOOP EXECUTE format('SELECT max(%I)+1 FROM %I.%I', row.id_name, sch, row.table_name) INTO row.next_value; EXECUTE format( 'CREATE SEQUENCE %I.%I START %L OWNED BY %I.%I.%I;', sch, row.table_name||'_'||row.id_name||'_seq', sch, row.table_name, row.id_name ); EXECUTE format( 'ALTER TABLE %I.%I ALTER COLUMN %I SET DEFAULT nextval(%L);' sch, row.table_name, row.id_name, row.table_name||'_'||row.id_name||'_seq' ); END LOOP; RETURN 'Sequences were created'; END; $BODY$; Agora vejamos... Em primeiro lugar eu não usei uma tabela temporário, simplesmente porque não vi sentido, e acho que acabou deixando a função mais confusa ainda. Ao invés disso eu apenas naveguei no resultado da consulta à view information_schema.tables. Repare também que não usei consulta dinâmica (comando EXECUTE) nesta consulta, simplesmente porque não é necessário e recomendo evitar o uso de EXECUTE ao máximo. Em segundo lugar, precisamos de três comandos, um para fazer um `SELECT max` para recuperar o maior valor, outro para criar a sequência e o último para alterar o DEFAULT da tabela. Em ambos os casos a função format ajuda bastante, e não precisa de tantos quote_ident's, que podem ser confusos. Em [1] há um comparativo legal entre format e quote_ident. Veja lá e poste dúvidas que sobrarem aqui. Por fim, adicionei a cláusula OWNED BY no CREATE SEQUENCE, para deixar idêntico ao que o pseudo-tipo SERIAL faz, e é uma boa prática para futuras manutenções. Só tenho mais um comentário. Esses comandos estão (talvez erroneamente) assumindo que toda tabela tem um campo "<nome da tabela>_id", mas talvez faça parte do seu modelo, mas eu evitaria isso, a não ser que seja algo executado somente numa tarefa administrativa. Ufa... Leia com calma, tentei ser o mais claro possível. ^^ [1] http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE Atenciosamente, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres
_______________________________________________ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral