Database object name resolution v15

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.name, where 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:

edb=# SHOW search_path;
Output
   search_path
-----------------
 "$user", public
(1 row)

$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 public.

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.

Note

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.