Acredito que para ficar mais elegante ainda faria umas pequenas
alterações!!!
CREATE OR REPLACE FUNCTION change_owner(oldOWNER TEXT, newOWNER TEXT)
RETURNS boolean AS $$
DECLARE
comando TEXT;
lista RECORD;
BEGIN
comando := 'ALTER SCHEMA ' || quote_ident(oldOWNER) || ' OWNER TO ' ||
quote_ident(newOWNER);
EXECUTE comando;
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=oldOWNER
AND pg_class.relkind IN ('r','v','S')
LOOP
IF lista.relkind = 'S' AND CAST((SELECT COALESCE(setting,0) FROM
pg_settings WHERE name = 'server_version_num') AS INTEGER) >= 80400 THEN
comando := 'ALTER SEQUENCE ' || quote_ident(lista.nspname) || '.' ||
quote_ident(lista.relname) || ' OWNER TO ' || quote_ident(newOWNER);
RAISE NOTICE '%', comando;
ELSE
comando := 'ALTER TABLE ' || quote_ident(lista.nspname) || '.' ||
quote_ident(lista.relname) || ' OWNER TO ' || quote_ident(newOWNER);
RAISE NOTICE '%', comando;
END IF;
EXECUTE comando;
END LOOP;
IF FOUND THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END;
$$
LANGUAGE 'plpgsql'
IMMUTABLE
RETURNS FALSE ON NULL INPUT;
2009/9/4 Fabrízio de Royes Mello <[email protected]>
>
>
> 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;
>>
>
> Se me permites adicionar um "plus" e uns ajustes :
>
> * alterar o dono do schema passado por parametro
> * colocar o schema.tabela nos ALTER ...
> * compatibilidade com versoes menores que 8.4
>
> CREATE OR REPLACE FUNCTION change_owner(TEXT,TEXT)
> RETURNS boolean AS $$
> DECLARE
> comando TEXT;
> lista RECORD;
> BEGIN
> comando := 'ALTER SCHEMA ' || quote_ident($1) || ' OWNER TO ' ||
> quote_ident($2);
> EXECUTE comando;
>
> 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' AND CAST((SELECT COALESCE(setting,0) FROM
> pg_settings WHERE name = 'server_version_num') AS INTEGER) >= 80400 THEN
> comando := 'ALTER SEQUENCE ' || quote_ident(lista.nspname) || '.' ||
> quote_ident(lista.relname) || ' OWNER TO ' || quote_ident($2);
> RAISE NOTICE '%', comando;
> ELSE
> comando := 'ALTER TABLE ' || quote_ident(lista.nspname) || '.' ||
> quote_ident(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;
>
>
> Gostei bastante dessa sua solução Jota... qdo eu precisava eu rodava um
> script gerando as DDLs... assim fica mais elegante e prático...
>
>
> Cordialmente,
> --
> 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
>
>
--
Abraços......
Marcel Araujo
System Analyst
Developer Java/PHP/RIA
Linux User #490101
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
