Обсуждение: BUG #15993: "CREATE OR REPLACE FUNCTION" does not clear search_path
The following bug has been logged on the website: Bug reference: 15993 Logged by: Alexander Pavlov Email address: alexander.pavlov.rus@gmail.com PostgreSQL version: 10.9 Operating system: Debian Description: -- this will correctly create function with empty search_path CREATE OR REPLACE FUNCTION MY_FUNC () RETURNS BOOLEAN AS $$ BEGIN RETURN TRUE;END;$$ LANGUAGE PLPGSQL; -- this will correctly update function to have "PUBLIC, MY_SCHEMA" search path CREATE OR REPLACE FUNCTION MY_FUNC () RETURNS BOOLEAN AS $$ BEGIN RETURN TRUE;END;$$ LANGUAGE PLPGSQL SET SEARCH_PATH TO PUBLIC, MY_SCHEMA; -- this will correctly update function to have "MY_SCHEMA" search path CREATE OR REPLACE FUNCTION MY_FUNC () RETURNS BOOLEAN AS $$ BEGIN RETURN TRUE;END;$$ LANGUAGE PLPGSQL SET SEARCH_PATH TO MY_SCHEMA; -- this will NOT update function to have empty search path, search path will remain "MY_SCHEMA", as at previous "create or replace" call CREATE OR REPLACE FUNCTION MY_FUNC () RETURNS BOOLEAN AS $$ BEGIN RETURN TRUE;END;$$ LANGUAGE PLPGSQL;
On Fri, Sep 6, 2019 at 08:42:02PM +0000, PG Bug reporting form wrote: > The following bug has been logged on the website: > > Bug reference: 15993 > Logged by: Alexander Pavlov > Email address: alexander.pavlov.rus@gmail.com > PostgreSQL version: 10.9 > Operating system: Debian > Description: > > -- this will correctly create function with empty search_path > CREATE OR REPLACE FUNCTION MY_FUNC () RETURNS BOOLEAN AS $$ BEGIN RETURN > TRUE;END;$$ LANGUAGE PLPGSQL; > > -- this will correctly update function to have "PUBLIC, MY_SCHEMA" search > path > CREATE OR REPLACE FUNCTION MY_FUNC () RETURNS BOOLEAN AS $$ BEGIN RETURN > TRUE;END;$$ LANGUAGE PLPGSQL SET SEARCH_PATH TO PUBLIC, MY_SCHEMA; > > -- this will correctly update function to have "MY_SCHEMA" search path > CREATE OR REPLACE FUNCTION MY_FUNC () RETURNS BOOLEAN AS $$ BEGIN RETURN > TRUE;END;$$ LANGUAGE PLPGSQL SET SEARCH_PATH TO MY_SCHEMA; > > -- this will NOT update function to have empty search path, search path will > remain "MY_SCHEMA", as at previous "create or replace" call > CREATE OR REPLACE FUNCTION MY_FUNC () RETURNS BOOLEAN AS $$ BEGIN RETURN > TRUE;END;$$ LANGUAGE PLPGSQL; So you are saying that CREATE OR REPLACE FUNCTION should not preserve the SEARCH_PATH. Makes sense to me. Any idea why we coded it this way? It it part of the "OR REPLACE" goal? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Bruce Momjian <bruce@momjian.us> writes: > So you are saying that CREATE OR REPLACE FUNCTION should not preserve > the SEARCH_PATH. Makes sense to me. Any idea why we coded it this way? We didn't. For me, it works as expected: ... follow example, \sf after each step regression=# \sf my_func CREATE OR REPLACE FUNCTION public.my_func() RETURNS boolean LANGUAGE plpgsql SET search_path TO 'my_schema' AS $function$ BEGIN RETURN TRUE;END;$function$ regression=# CREATE OR REPLACE FUNCTION MY_FUNC () RETURNS BOOLEAN AS $$ BEGIN RETURN regression$# TRUE;END;$$ LANGUAGE PLPGSQL; CREATE FUNCTION regression=# \sf my_func CREATE OR REPLACE FUNCTION public.my_func() RETURNS boolean LANGUAGE plpgsql AS $function$ BEGIN RETURN TRUE;END;$function$ regards, tom lane