On 2023-09-26 14:44 +0200, dld wrote: > I followed the discussion about the schema resolution, and I really think > there is need for an early bound (at function definition time) version of > CURRENT_SCHEMA (the first member of search_path) You mean something like CREATE FUNCTION myfunc() RETURNS void SET search_path = CURRENT_SCHEMA ... where CURRENT_SCHEMA would resolve to the current schema instead of meaning literal "CURRENT_SCHEMA"? > Avoiding hard-coding of schema names, (and avoiding polluting the actual > users schema) is hard. > > My current code generates some plpgsql functions, which need some helper > functions to construct fcolumn lists, query fragments, etc. > > These helpers should live in the same schema, IMHO The helper functions can't be created in a common schema with a fixed name? > It is not impossible: I ended up with the following kludge to refer to > functions in the same schema as the calling function. > > It involves an extra layer of dynamic SQL, which self-destructs after use. > It is not pretty, but it works. You can do without the self-dropping disposable_factory() by using a DO block instead. > Example of such a nested dynamic function is attached. (the helper functions > are not included, but they live in the same "compilation unit") It's already possible to "inject" the current schema name by setting the search path after creating the function. Define the function as usual but with unqualified names in the function body and then alter the function to set the search path to the value of current_schema. BEGIN; CREATE FUNCTION create_asof(_fqn text, asof_date text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE basepair text[]; BEGIN basepair := split_name(_fqn); -- etc. END $$; DO $$ BEGIN EXECUTE format( 'ALTER FUNCTION create_asof(text, text) SET search_path = %I', current_schema); END $$; COMMIT; By the looks of it, your create_asof() creates functions with qualified names provided in variable fnc_name. So the executed CREATE FUNCTION statement is not subject to the current search path. -- Erik