Re: Current Schema for Functions called within other Functions

Merlin,

Perfect. Thank you.

Best,
--Lee

On Mon, Apr 1, 2013 at 10:04 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Sun, Mar 31, 2013 at 5:32 PM, Lee Hachadoorian
> <Lee(dot)Hachadoorian+L(at)gmail(dot)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/