How does the search_path influence identifier resolution and the "current schema"
What is the schema search path search_path
?
The manual:
[...] tables are often referred to by unqualified names, which consist of just the table name. The system determines which table is meant by following a search path, which is a list of schemas to look in.
Bold emphasis mine. This explains identifier resolution.
The “current schema” (or “default schema”) is, per documentation:
The first schema named in the search path is called the current schema. Aside from being the first schema searched, it is also the schema in which new tables will be created if the
CREATE TABLE
command does not specify a schema name.
Bold emphasis mine. The system schemas pg_temp
(schema for temporary objects of the current session) and pg_catalog
are automatically part of the search path and searched first, in this order. The manual:
pg_catalog
is always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched before searching the path's schemas. This ensures that built-in names will always be findable. However, you can explicitly placepg_catalog
at the end of your search path if you prefer to have user-defined names override built-in names.
Bold emphasis as per original. And pg_temp
comes before that, unless it's put into a different position.
How to set it?
There are various ways to set the runtime variable search_path
.
Set a cluster-wide default for all roles in all databases in
postgresql.conf
(and reload). Careful with that!search_path = 'blarg,public'
The shipped default for this setting is:
search_path = "$user",public
The first element specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored.
Set it as default for one database:
ALTER DATABASE test SET search_path = blarg,public;
Set it as default for the role you connect with (effective cluster-wide):
ALTER ROLE foo SET search_path = blarg,public;
Or even (often best!) as default for a role in a database:
ALTER ROLE foo IN DATABASE test SET search_path = blarg,public;
Write the command at the top of your script. Or execute it in your DB session:
SET search_path = blarg,public;
Set a specific
search_path
for the scope of a function (to be safe from malicious users with sufficient privileges). Read about WritingSECURITY DEFINER
Functions Safely in the manual.
CREATE FUNCTION foo() RETURNS void AS
$func$
BEGIN
-- do stuff
END
$func$ LANGUAGE plpgsql SECURITY DEFINER
SET search_path=blarg,public,pg_temp;
Higher number in my list trumps lower number.
The manual has even more ways, like setting environment variables or using command-line options.
To see the current setting:
SHOW search_path;
To reset it:
RESET search_path;
The manual:
The default value is defined as the value that the parameter would have had, if no
SET
had ever been issued for it in the current session.
Search path is indeed what you want:
% create schema blarg;
% set search_path to blarg;
% create table foo (id int);
% \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
blarg | foo | table | pgsql