I have schema1 in database1. I want to move all the functions of schema1 to schema2 which is present in database2. I have restored backup file of database1 into database2. And changed the schema name. The schema name for function call automatically got changed. But within function definition the schema name is not changed. for ex:
CREATE OR REPLACE FUNCTION schema2.execute(..)
BEGIN
select schema1."VALIDATE_SESSION"(....)
end
How can I change "schema1" to "schema2" automatically?
I have tried to store current schema name in variable and append it to table. But calling current_schema() returns "public". How to get current schema created by user? Because every time I need to change the schema name while generating script.
The essential detail that is missing in your dummy function are the single quotes (or dollar-quotes, all the same) around the function body. Meaning, function bodies are saved as strings. See:
To contrast, consider a reference to a table (or more verbosely: schema.table(column)) in a FK constraint. Object names are resolved to the internal OID of the table (and a column number) at creation time. "Early binding". When names (including schema names) are changed later, that has no effect on the FK at all. Feels like involved names are changed dynamically. But really, actual names just don't matter after the object has been created. So you can rename schemas all day without side effect for the FK.
Names in a function body are stored as strings and interpreted at call time. "Late binding". Those names are not changed dynamically.
Since Postgres 14 there is an exception to this rule, but only for LANGUAGE sql functions: "SQL-standard function bodies". Those are parsed at creation time, so they bind early, and track the OID of a schema, not its name. See:
Other than that, you'll have to actually edit all function bodies including a hard-coded schema name. A possible alternative is to rely on the search_path instead and not use schema names in function bodies to begin with. There are various. See:
But that's not always acceptable.
You could hack the dump. Or use sting manipulation inside Postgres to update affected function bodies. Find affected functions with a meta-query like:
SELECT *
FROM pg_catalog.pg_proc
WHERE prosrc ~ '\mschema1\M'; -- not bullet-proof!
Either way, be wary of false matches if the schema name can be part of other strings or pop up as column name etc. And dynamic SQL can concatenate strings in arbitrary ways. If you have such evil trickery in your functions, you need to deal with it appropriately.