Postgres allows us to rename a schema with the command alter schema schema_orig rename to schema_dest. however the definition of functions belonging to the original schema are not modified.
CREATE OR REPLACE FUNCTION foo.fcount() RETURNS integer AS $$
declare
v_count integer;
BEGIN
select count(*)
into v_count
from foo.test;
return v_count;
-- raise notice 'The number is: %', v_count;
END;
$$ LANGUAGE plpgsql;
select fcount();
fcount
2
(1 row)
alter schema foo rename to bar;
select fcount();
ERROR: relation "foo.test" does not exist
LINE 2: from foo.test
^
Implementing the remap feature seemed to me feasible since this logic was similar to that of the native rename function.
Oracle provides this option for its export tool.
But I will not go further in this way I don't want to go against the advice of experts who have been working on the postgres code for years.
Thank you for your comments
Regards,
Fabrice
On Wed, Nov 16, 2022 at 3:42 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Guillaume Lelarge <guillaume@xxxxxxxxxxxx> writes:
> Le mer. 16 nov. 2022 à 13:08, Fabrice Chapuis <fabrice636861@xxxxxxxxx> a
> écrit :
>> I worked on the pg_dump source code to add remap schema functionality to
>> use it internally where I work. This is a first version that allows to
>> remap tables, views and sequences (only to export schema). Is this
>> development likely to interest the PG community and to continue this
>> development further?
> If it doesn't work on functions, that will be a big issue. And to be
> honest, I don't think you can do it reliably on functions, especially with
> dynamic queries in PL/pgsql.
Yeah --- I fear there is no hope of making a feature like this that
works reliably enough that we'd accept it. pg_restore is just not
that smart about what is in the chunks of DDL that it processes,
and trying to make it smart enough is a losing game.
regards, tom lane
