create procedures in different schema without schema name and without altering search_path

user_5944884 0
Viewed 129

I have a list of procedures where 25% of procedures need to be compiled in a particular schema likewise for the rest of procedures, totally there are 5 schemas.

No procedure will exist in more than one schema.

While creating/compiling, I cannot use the schema prefix before the procedure names. The procedure can have database objects from different schemas. As of now, I am required to alter the search_path every time when compiling a procedure(based on which schema it needs to reside).

Is there any easier alternate to do this ?

postgresql
1 Answers

Making a function dependent on the search_path in the current session sounds fragile.

How about using dynamic SQL?

CREATE PROCEDURE do_something(p_schema text)
   LANGUAGE plpgsql AS
$$DECLARE
   old_search_path text;
BEGIN
   old_search_path := current_setting('search_path');
   EXECUTE format('SET LOCAL search_path = %I', p_schema);
   /* so something */
   EXECUTE format('SET LOCAL search_path = %s', old_search_path);
END;$$;
user_64643080
Related