Moving functions to other schemas

As with many other database objects, it is possible to place a function into a schema that is different from the default one, which is public. All we need to do is either fully qualify the name of the function at the time it is created or issue an ALTER FUNCTION command.

Listing 19 shows the two ways to add the function f_greetings() to the schema my_library:

1. By declaring the function with the schema qualifier before its name, such as my_library.f_greetings
2. By issuing an ALTER FUNCTION SET SCHEMA command to move the function from one schema to another

      -- first ensure there is the schema
testdb=> CREATE SCHEMA my_library;
      -- and then add the function to the schema
testdb=> CREATE OR REPLACE FUNCTION
      my_library.f_greetings( who text )
      RETURNS text AS $code$
         BEGIN
            RETURN 'Hello dear ' || who;
         END $code$ LANGUAGE plpgsql IMMUTABLE;

     -- or to move an already existing function
testdb=> ALTER FUNCTION f_greetings( text ) SET SCHEMA my_library;

For security reasons, it is not possible to change the schema of an existing function into the pg_temp one, since this would transform the function into a temporary one. Similarly, a function that has been already defined as temporary cannot be moved to a non-temporary schema.