Exemplificando melhor
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;';
Atenciosamente,
Anderson Abreu
[email protected]
(61) 8157-6657
OOAD, RUP, SOA, Jboss, IIS
.Net, PHP, CFM, ASP, JAVA
McAffee Partner - Microsoft Partner
MySQL, Oracle, PostgreSQL, SQL Server, DB2, SyBase
2014/1/17 Eloi Ribeiro <[email protected]>
> 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';
>
> Obrigado,
>
> --
> Eloi Ribeiro
> GIS Analyst / Programmer
> 51.9871, 5.6661
> flavors.me/eloiribeiro
> _______________________________________________
> pgbr-geral mailing list
> [email protected]
> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
>
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
