Thread: BUG #15101: function set search_path = '' breaks dump/restore

The following bug has been logged on the website:

Bug reference:      15101
Logged by:          Andrew Gierth
Email address:      andrew@tao11.riddles.org.uk
PostgreSQL version: 10.3
Operating system:   any
Description:

Given:

create or replace function foo()
 returns text language sql
 as $$
  select current_schemas(true)::text;
 $$
 set search_path = '';

dump/restore (or \ef) fails with:

ERROR:  zero-length delimited identifier at or near """"

The problem obviously being that pg_get_functiondef is returning invalid
syntax for the search_path setting.

On Thu, Mar 08, 2018 at 06:59:21AM +0000, PG Bug reporting form wrote:
> create or replace function foo()
>  returns text language sql
>  as $$
>   select current_schemas(true)::text;
>  $$
>  set search_path = '';
>
> dump/restore (or \ef) fails with:
>
> ERROR:  zero-length delimited identifier at or near """"
>
> The problem obviously being that pg_get_functiondef is returning invalid
> syntax for the search_path setting.

In this context pg_get_functiondef assigns quote as it sees that
search_path is of type GUC_LIST, which needs special handling.  Likely
this needs more thoughts for empty values.
--
Michael

>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:

 PG> Given:

 PG> create or replace function foo()
 PG>  returns text language sql
 PG>  as $$
 PG>   select current_schemas(true)::text;
 PG>  $$
 PG>  set search_path = '';

For the record, the workaround I suggested to the user on IRC who ran
into this issue was:

alter function foo() set search_path = pg_catalog;

which has (as far as I can tell) exactly the same runtime effect as
setting it to '' but doesn't break dump/restore.

-- 
Andrew (irc:RhodiumToad)