Em 17 de janeiro de 2014 21:22, Matheus de Oliveira < [email protected]> escreveu:
> > > > On Fri, Jan 17, 2014 at 11:54 AM, Eloi Ribeiro <[email protected]>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 > [email protected] > https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral > > Boa Matheus, bom saber que penso da mesma forma que os veteranos da lista.. hehehe Ia explicar da mesma forma, mas não tive tempo e o assunto ficou bem confuso. []'s
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
