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.
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").
