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
