On Sun, Mar 31, 2013 at 5:32 PM, Lee Hachadoorian <Lee.Hachadoorian+L@xxxxxxxxx> wrote: > 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? in plpgsql, all functions and tables that are not schema qualified become schema qualified when the function is invoked and planned the first time. The line: ql := current_schema() || ',' || get_var(); attaches a silent var1. to get_var() so it will forever be stuck for that connection. The solution is to use EXECUTE. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
