Ahh, uma pequena alteração, me esqueci que não é permitido retorno de
valores como FALSE ON NULL INPUT então o correto seria
$$
LANGUAGE 'plpgsql'
IMMUTABLE
RETURNS NULL ON NULL INPUT;
2009/9/5 Marcel Araujo <ceceld...@gmail.com>
> 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 <fabriziome...@gmail.com>
>
>>
>>
>> 2009/9/4 JotaComm <jota.c...@gmail.com>
>>
>>> 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
>> pgbr-geral@listas.postgresql.org.br
>> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
>>
>>
>
>
> --
> Abraços......
>
> Marcel Araujo
> System Analyst
> Developer Java/PHP/RIA
> Linux User #490101
>
--
Abraços......
Marcel Araujo
System Analyst
Developer Java/PHP/RIA
Linux User #490101
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
