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
