Question 114620 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – schema prefixes in postgres , scope of called elements

Will schema-prefixing a stored procedure also limit the scope of all the procedures called inside it to that schema?

Take the following scenerio in consideration:

Say I have a stored procedure parentProc that calls proc childA childB and childC. This procedure is available in all schemas SchemaA, public and SchemaB like this:

create or replace procedure parentProc(){
  CALL childA;
  CALL childB;
  CALL childC;
}

SchemaA
|_parentProc
|_childA
|_childB
|_ChildC
SchemaB
|_parentProc
|_childA
|_childB
|_ChildC
public
|_parentProc
|_childA
|_childB
|_ChildC

the default search path is public, so when I call dbo.parentProc will the procedure in the same schema(dbo) be called or from the public?

If childA, childB and childC are views instead, what will happen in that scenerio? Will the parentproc being prefixed scope the views to that schema?

Tags:

2

Answers


  1. 0

    From the manual:

    search_path (string) This variable specifies the order in which
    schemas are searched when an object (table, data type, function, etc.)
    is referenced by a simple name with no schema specified. When there
    are objects of identical names in different schemas, the one found
    first in the search path is used. An object that is not in any of the
    schemas in the search path can only be referenced by specifying its
    containing schema with a qualified (dotted) name.

    That means that any call to a procedure without a schema qualification, will only find (or not) the procedure that is in the public schema. Because public is in your default search path.

    I always use just public for the default, the rest is using a schema qualification.

    Login or Signup to reply.
  2. 0

    If you reference one object using the schema, that doesn’t change the fact that all unqualified references use the search_path to determine the schema. So if you

    CALL schemaa.parentproc();

    but search_path is schemab, the procedure will still call the procedures from schemab.

    The same holds for views or all other objects, but note that the objects referenced inside a view do not depend on the setting of search_path when the view is used, but on the setting of search_path when the view was defined. That is because the defining query is parsed at view creation time and stored in parsed form.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search