Valeu pessoal
2009/9/4 JotaComm <[email protected]>: > Olá, > > Segue a minha solução: > > CREATE OR REPLACE FUNCTION change_owner(TEXT,TEXT) > RETURNS boolean AS $$ > DECLARE > comando TEXT; > lista RECORD; > BEGIN > FOR lista IN > SELECT pg_namespace.nspname,pg_class.relname,pg_class.relkind > FROM pg_namespace JOIN pg_class ON > pg_namespace.oid=pg_class.relnamespace > WHERE pg_namespace.nspname=$1 > AND pg_class.relkind IN (‘r’,‘v’,‘S’) > LOOP > IF lista.relkind=‘S’ THEN > comando:=‘ALTER SEQUENCE ‘ || lista.relname || ‘ OWNER TO ‘ || > quote_ident($2); > RAISE NOTICE ‘‘, comando; > ELSE > comando:=‘ALTER TABLE ‘ || lista.relname || ‘ OWNER TO ‘ || > quote_ident($2); > RAISE NOTICE ‘‘, comando; > END IF; > EXECUTE comando; > END LOOP; > IF FOUND THEN > RETURN TRUE; > END IF; > RETURN FALSE; > END; > $$ LANGUAGE PLPGSQL; > > Chamada da função: > > SELECT change_owner(‘nome_do_esquema’,‘usuario_a_ser_dono_do_objeto’); > > 2009/9/4 Fabrízio de Royes Mello <[email protected]> >> >> 2009/9/4 JotaComm <[email protected]> >> > >> > <corte> >> >> >> >> 2009/9/4 JotaComm <[email protected]>: >> >> > Olá, Jorge >> >> > >> >> > Existe sim. É só fazer um função utilizando a linugagem PL/pgSQL para >> >> > conseguir isso. >> >> > >> >> > Dica: Utilize SQL diânimco. >> >> >> >> A dica do Jota de criar uma PL/pgSQL mais o SQL abaixo você está com a >> solução "quase" pronta: >> >> select 'ALTER TABLE '||table_schema||'.'||table_name||' OWNER TO >> nome_do_usuario;' >> from information_schema.tables >> where table_schema not in ('pg_catalog', 'information_schema', 'pg_toast') >> and table_schema !~ '^pg_temp' >> >> union all >> >> select 'ALTER TABLE '||sequence_schema||'.'||sequence_name||' OWNER TO >> nome_do_usuario;' >> from information_schema.sequences >> where sequence_schema not in ('pg_catalog', 'information_schema', >> 'pg_toast') >> and sequence_schema !~ '^pg_temp' >> >> union all >> >> select 'ALTER SCHEMA '||schema_name||' OWNER TO nome_do_usuario;' >> from information_schema.schemata >> where schema_name not in ('pg_catalog', 'information_schema', 'pg_toast') >> and schema_name !~ '^pg_temp'; >> >> >> Os SELECTs acima geram os ALTERs necessários para modificar o dono dos >> objetos TABLE, SEQUENCE e SCHEMA para o usuário denominado >> *nome_do_usuario*. >> >> Espero ter ajudado... >> >> -- >> Fabrízio de Royes Mello >> >> Blog sobre TI: http://fabriziomello.blogspot.com >> _______________________________________________ >> pgbr-geral mailing list >> [email protected] >> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral > > > []s > -- > JotaComm > http://jotacomm.wordpress.com > http://www.dextra.com.br/postgres > > _______________________________________________ > 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
