Merlin, Perfect. Thank you.
Best, --Lee On Mon, Apr 1, 2013 at 10:04 AM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Sun, Mar 31, 2013 at 5:32 PM, Lee Hachadoorian > <lee.hachadooria...@gmail.com> 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 > -- Lee Hachadoorian Asst Professor of Geography, Dartmouth College http://freecity.commons.gc.cuny.edu/