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.
Guillaume Lelarge <guillaume@lelarge.info> writes: > Le mer. 16 nov. 2022 à 13:08, Fabrice Chapuis <fabrice636861@gmail.com> 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.
