On 12/25/23 09:29, Wilma Wantren wrote:
Great that you have discussed this further! And sorry that what I wrote is apparently still misleading.
Peter understood me correctly, "__function_schema__ always refers to the schema the function actually is in".
I define:
1. create function my_schema.function1...
2. alter function function1 set search_path to __function_schema__
The variable __function_schema__ is not evaluated, but is set "as it is" in the metadata of the function, which therefore reads:
...
search_path: __function_schema__
...
Only when function1 is executed the variable is evaluated, namely "to the schema the function actually is in", in this case to 'my_schema'. If the schema is renamed, the search_path of function1 does not have to be changed, and is nevertheless evaluated correctly again when the function is executed, now to the new schema name.
Alright I see now.
A simple example of a partial solution:
CREATE OR REPLACE FUNCTION public.schema_test()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
fnc_name varchar := 'schema_test';
sch_name varchar;
cur_sch name;
BEGIN
SELECT INTO sch_name pronamespace::regnamespace from pg_proc where
proname = fnc_name;
RAISE NOTICE 'Schema is %', sch_name;
PERFORM set_config('search_path', sch_name, true);
select into cur_sch current_schema;
RAISE NOTICE 'search_path = %', cur_sch;
END;
Issues:
fnc_name would need to kept updated.
Overloaded function names would need to be accounted for.
Functions with same name in different schemas would need to be dealt with.
At this point I still the solution as being external to the database. In
other words making the change before the objects are loaded into a database.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
