Current Schema for Functions called within other Functions

I'm working on some PL/pgSQL functions to generate dynamic SQL. The
functions live in the public schema, but the SQL generated will vary
depending on what schema they are called from. Something is going on which
I cannot figure out. I am defining "variables" by creating a getter
function within each schema. This getter function is then called by the
dynamic SQL function. But this works once, and then the value seems to
persist.

```SQL
CREATE SCHEMA var1;
CREATE SCHEMA var2;

SET search_path = public;

/*This function generates dynamic SQL, here I have it just returning a
string
with the current schema and the value from the getter function.*/
DROP FUNCTION IF EXISTS sql_dynamic();
CREATE FUNCTION sql_dynamic() RETURNS text AS $function$
DECLARE
sql TEXT := '';
BEGIN
sql := current_schema() || ',' || get_var();
RETURN sql;
END;
$function$ LANGUAGE plpgsql;

SET search_path = var1, public;

SELECT get_var(); --Fails
SELECT sql_dynamic(); --Fails

DROP FUNCTION IF EXISTS get_var();
CREATE FUNCTION get_var() RETURNS text AS $get_var$
BEGIN
RETURN 'var1';
END;
$get_var$ LANGUAGE plpgsql;

SELECT get_var();
SELECT sql_dynamic();

SET search_path = var2, public;

SELECT get_var(); --Fails
SELECT sql_dynamic(); --Returns current_schema and *old* get_var() value
from wrong schema!

DROP FUNCTION IF EXISTS get_var();
CREATE FUNCTION get_var() RETURNS text AS $get_var$
BEGIN
RETURN 'var2';
END;
$get_var$ LANGUAGE plpgsql;

SELECT get_var(); --Succeeds
SELECT sql_dynamic(); --Still returns *old* get_var() value from wrong
schema!

```

At this point, if I DROP/CREATE sql_dyanamic() in public, get_var() run in
the var2 schema works, but if I change the search_path back to var1,
sql_dynamic() returns "var1,var2".

I also tried using a table to store the variable. I created a table var
(with one field also named var) in each schema, then altered sql_dynamic()
to return current_schema() and the value of var.var (unqualified, so that
expected when search_path includes var1 it would return var1.var.var), but
I ran into the same persistence problem. Once "initialized" in one schema,
changing search_path to the other schema returns the correct current_schema
but the value from the table in the *other* schema (e.g. "var2,var1").

What am I missing?

Thanks,
--Lee

--
Lee Hachadoorian
Asst Professor of Geography, Dartmouth College
http://freecity.commons.gc.cuny.edu/