On Fri, 27 Dec 2024 13:26:28 -0700 "David G. Johnston" <david.g.johnston@xxxxxxxxx> wrote: > > Or is it documented somewhere? > > https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING I can't find any notes regarding functions and schemas in that section. > Can someone explain to me what's going on, and what is the best practice to > > deal with it? Is there a way to avoid fully qualifying every type and > > expression? Which parts do I have to qualify or is this something that > > could be fixed in a future version of PostgreSQL? > > > > Add qualification or attach a “set search_path” clause to “create > function”. Code stored in the server should not rely on the session > search_path. > > David J. In my (real world) case, I was unable to use "SET search_path FROM CURRENT" because it isn't possible to use "SET" in procedures that use transactions, due to this documented limitation: "If a SET clause is attached to a procedure, then that procedure cannot execute transaction control statements (for example, COMMIT and ROLLBACK, depending on the language)." https://www.postgresql.org/docs/17/sql-createprocedure.html My procedure looks more or less like this: CREATE PROCEDURE "myfunc"() LANGUAGE plpgsql AS $$ DECLARE "old_search_path" TEXT; -- some more variables BEGIN SELECT current_setting('search_path') INTO "old_search_path"; SET search_path TO 'myschema'; -- some code that uses COMMIT and SET TRANSACTION ISOLATION LEVEL PERFORM set_config('search_path', "old_search_path", FALSE); END; $$; My question is: Am I safe if I use fully-qualified types in the DECLARE section only? Or do I need to provide full qualification also in the code below (after SET search_path TO 'myschema')? And bonus question: Is it documented somewhere? Maybe not many people run into these issues because schemas and functions aren't used as often in combination? Kind Regards Jan Behrens
