Re: Current Schema for Functions called within other Functions

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