Thread: BUG #16023: in PgAdmin4.13, unable to recompile functions that have "search_path" set at the function level

The following bug has been logged on the website:

Bug reference:      16023
Logged by:          William Burke
Email address:      william.burke@collins.com
PostgreSQL version: 9.4.0
Operating system:   Windows 10 64bit
Description:

Our company has been adding the search_path to all of our functions in order
to tighten up security by using the following syntax in psql - notice I do
not add single quotes around the search_path values:

ALTER FUNCTION function_name(text, text, text) SET search_path=schema1,
pg_temp;

This works fine, however, when we then open the function in pgAdmin 4.13 (by
right clicking on the function from the pgAdmin browser window and choosing
Scripts -> CREATE Script), it is displaying the search path with single
quotes around it (they were added by pgAdmin4 query tool window), as shown
in the sample function below. This throws an error on our "custom type
variable" inside the function every time we try to recompile this function.
If the single quotes around the search_path are removed, the function will
recompile fine without an error. A sample function and sample error are
provided below:

Sample Function as shown in pgAdmin4.13:
CREATE OR REPLACE FUNCTION schema1.function1(
    parameter1 text,
    parameter2 text,
    parameter3 text)
RETURNS record
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE SECURITY DEFINER 
    SET search_path='schema1, pg_temp'
AS $BODY$
DECLARE
   v_typeValue   customType;

END;
$BODY$;

Sample of the error message returned:
ERROR:  type "customType" does not exist
LINE 42:    v_typeValue        customType;
                            ^
SQL state: 42704
Character: 1366

PG Bug reporting form <noreply@postgresql.org> writes:
> PostgreSQL version: 9.4.0

> Sample Function as shown in pgAdmin4.13:
> CREATE OR REPLACE FUNCTION schema1.function1(
>     parameter1 text,
>     parameter2 text,
>     parameter3 text)
> RETURNS record
>     LANGUAGE 'plpgsql'
>     COST 100
>     VOLATILE SECURITY DEFINER 
>     SET search_path='schema1, pg_temp'
> AS $BODY$
> DECLARE
>    v_typeValue   customType;

If this is really a 9.4.0 server, updating might help this, as
there were relevant fixes in 9.4.18 and again in 9.4.19.
(Not to mention all the other bug fixes you're missing.)

I get sane-looking behavior in 9.4.24:

regression=# create function foo() returns int as 'select 1' language sql;
CREATE FUNCTION
regression=# alter function foo() SET search_path=schema1, pg_temp;
ALTER FUNCTION
regression=# \sf foo()
CREATE OR REPLACE FUNCTION public.foo()
 RETURNS integer
 LANGUAGE sql
 SET search_path TO 'schema1', 'pg_temp'
AS $function$select 1$function$
regression=# \q

Now, psql's \sf depends on pg_get_functiondef(), but I don't know
whether pgAdmin does or not.  So it may be that the quoting foulup is
actually pgAdmin's fault, in which case you need to complain on
the pgadmin lists.  This list is just for bugs in core Postgres.

            regards, tom lane