schema prefixes in postgres , scope of called elements

mleko 0
Viewed 44

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?

schema postgresql
2 Answers

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.

Laurenz Albe0

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.

Frank Heikens0
Related