Criação automática de vários Schemas no PostgreSQL

Autor: Rondinele Couto

----------------

CREATE FUNCTION insert_schema(num INT) RETURNS VOID AS $$

DECLARE banco text;

DECLARE comment text;

BEGIN

FOR i IN 1..num LOOP

banco := 'cliente'||to_char(i,'fm000');

EXECUTE 'CREATE SCHEMA ' || banco || ' AUTHORIZATION agenciasigna';

comment := Chr(39) || banco || Chr(39);

EXECUTE 'COMMENT ON SCHEMA ' || banco || ' IS ' || comment;

END LOOP;

END;

$$

LANGUAGE plpgsql;

SELECT insert_schema(100);