0

As part of an ETL process, I am trying to change the schema of a recursive function. Like 

CREATE FUNCTION some_recursive()
 RETURNS some_type
 LANGUAGE sql
AS $function$ 
 -- do stuff
 some_recursive()
 --do more
$function$
;

I do this using 

ALTER FUNCTION some_recursive() SET SCHEMA new_schema

This correctly updates e.g. dependent views, changing them from calling some_recursive() to new_schema.some_recursive(). But the recursion itself (within the function body) is not updated, so when the function is called, it fails. I.e. the function definition post schema change looks like:

CREATE FUNCTION new_schema.some_recursive()
 RETURNS some_type
 LANGUAGE sql
AS $function$ 
 -- do stuff
 -- note the next line (no new_schema.-prefix)
 some_recursive()
 --do more
$function$
;

Is there a way to change the schema of a recursive function?

Failing that, I might be able to have the functions maintainer change its implementation. Is there a way to have the function call something like "self" (i.e. implicitly without specifying its own name)?

AdamAL
  • 1,571
  • 2
  • 14
  • 25

1 Answers1

1

The search_path used in the function depends on the search_path set in the session that calls the function. You can change this by default for whatever user is calling the function by altering the user:

ALTER USER some_user SET search_path TO new_schema,...;  -- you may also want public or others after new_schema

Alternatively, you can change the search_path used in just the function by modifying the code slightly:

CREATE FUNCTION new_schema.some_recursive()
 RETURNS some_type
 LANGUAGE sql
 SET search_path TO new_schema -- this line changes search_path while inside the function.  It will be reset on function exit.
AS $function$ 
 -- do stuff
 -- note the next line (no new_schema.-prefix)
 some_recursive()
 --do more
$function$
;

You could also use PG_CONTEXT as outlined here: https://stackoverflow.com/a/41889304/895640 but it's a bit hacky...

CREATE OR REPLACE FUNCTION recursive_test() RETURNS int AS $$
DECLARE
  stack text;
  fcesig regprocedure;
BEGIN
  GET DIAGNOSTICS stack = PG_CONTEXT;
  RAISE NOTICE 'stack: %', stack;
  fcesig := substring(stack from 'function (.*?) line');
  EXECUTE 'SELECT ' || fcesig;
END;
$$ LANGUAGE plpgsql
;

CREATE SCHEMA test2;
alter function recursive_test set schema test2;
NOTICE:  stack: PL/pgSQL function test2.recursive_test() line 6 at GET DIAGNOSTICS
NOTICE:  stack: PL/pgSQL function test2.recursive_test() line 6 at GET DIAGNOSTICS
SQL statement "SELECT test2.recursive_test()"
PL/pgSQL function test2.recursive_test() line 9 at EXECUTE
NOTICE:  stack: PL/pgSQL function test2.recursive_test() line 6 at GET DIAGNOSTICS
SQL statement "SELECT test2.recursive_test()"
PL/pgSQL function test2.recursive_test() line 9 at EXECUTE
SQL statement "SELECT test2.recursive_test()"
PL/pgSQL function test2.recursive_test() line 9 at EXECUTE
NOTICE:  stack: PL/pgSQL function test2.recursive_test() line 6 at GET DIAGNOSTICS
SQL statement "SELECT test2.recursive_test()"
PL/pgSQL function test2.recursive_test() line 9 at EXECUTE
SQL statement "SELECT test2.recursive_test()"
PL/pgSQL function test2.recursive_test() line 9 at EXECUTE
...
Jeremy
  • 6,313
  • 17
  • 20
  • 1
    This is a good workaround. A few notes: The `search_path` can also be set at the database level (for any user) with `ALTER DATABASE the_db SET search_path TO ...`. Worth noting that either only take effect for new sesssions. To affect the current session call `SET search_path ...` (i.e. without `ALTER...`) – AdamAL Jun 18 '19 at 20:35
  • Also, this would not work if the function definition had schema-qualified it's self reference. – AdamAL Jun 19 '19 at 07:34
  • I edited my response with another method that would work, but I'm not sure I'd recommend actually using it... – Jeremy Jun 19 '19 at 14:25