Hi Tom,
Thank you for your prompt response.
When I use pg_dump to export schema from an older version of PostgreSQL 8.2.3 , it adds the following line at the beginning:
SET search_path = public, pg_catalog;
Is it possible set an option where pg_dump will add this line in PostgreSQL 12.5?
At 2022-08-25 11:07:46, "Tom Lane" <tgl@xxxxxxxxxxxxx> wrote: >gzh <gzhcoder@xxxxxxx> writes: >> When I use pg_dump to export schema from a database, it adds the following line at the beginning: >> SELECT pg_catalog.set_config('search_path', '', false); >> Is it possible set an option where pg_dump will not add this line? > >No. It's a security precaution. > >> It is causing issues later when I try to execute other SQL commands, without the schema qualifier. > >That probably means you have some user-defined functions that are >not sufficiently careful about search_path. You want to either >schema-qualify every reference in the function body, or attach a >"SET search_path" clause to remove the function's context dependency. > >(If you're using SQL-language functions in v14 or later, another >option is to use the SQL-standard syntax instead of writing the >body as a string literal. Then the object references in the function >are parsed at definition time.) > > regards, tom lane
