Database object name resolution v16
You can reference a database object inside an SPL program either by its qualified name or by an unqualified name. A qualified name is in the form of
schema is the name of the schema under which the database object with identifier
name exists. An unqualified name doesn't have the
schema. portion. When a reference is made to a qualified name, there is no ambiguity as to which database object is intended. It either does or doesn't exist in the specified schema.
Locating an object with an unqualified name, however, requires the use of the current user’s search path. When a user becomes the current user of a session, a default search path is always associated with that user. The search path consists of a list of schemas that are searched in left-to-right order for locating an unqualified database object reference. The object is considered nonexistent if it can’t be found in any of the schemas in the search path. You can display the default search path in PSQL using the
SHOW search_path command:
$user in the search path is a generic placeholder that refers to the current user. So if the current user of this session is
enterprisedb, an unqualified database object is searched for in the following schemas in this order: first in
enterprisedb, and then in
Once an unqualified name is resolved in the search path, you can determine if the current user has the appropriate privilege to perform the desired action on that specific object.
The concept of the search path isn't compatible with Oracle databases. For an unqualified reference, Oracle looks in the schema of the current user for the named database object. Also, in Oracle, a user and their schema is the same entity. In EDB Postgres Advanced Server, a user and a schema are two distinct objects.