Database Object Name Resolution v13

A database object inside an SPL program may either be referenced 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 does not have the schema. portion. When a reference is made to a qualified name, there is absolutely no ambiguity as to exactly which database object is intended – it either does or does not 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 which are searched in left-to-right order for locating an unqualified database object reference. The object is considered non-existent if it can’t be found in any of the schemas in the search path. The default search path can be displayed in PSQL using the SHOW search_path command.

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

$user in the above search path is a generic placeholder that refers to the current user so if the current user of the above session is enterprisedb, an unqualified database object would be searched for in the following schemas in this order – first, enterprisedb, then public.

Once an unqualified name has been resolved in the search path, it can be determined if the current user has the appropriate privilege to perform the desired action on that specific object.

Note

The concept of the search path is not compatible with Oracle databases. For an unqualified reference, Oracle simply looks in the schema of the current user for the named database object. It also important to note that in Oracle, a user and his or her schema is the same entity while in Advanced Server, a user and a schema are two distinct objects.