Postgres search_path - ghdrako/doc_snipets GitHub Wiki

The search path effectively sets a search order, and PostgreSQL uses the first matching object it finds. This mechanism allows you to control which schema’s objects take precedence when multiple schemas contain objects with the same name.

There are various ways to set the runtime variable search_path.

  1. 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
  1. Set it as default for one database:
ALTER DATABASE test SET search_path = blarg,public;
  1. Set it as default for the role you connect with (effective cluster-wide):
ALTER ROLE foo SET search_path = blarg,public;
  1. or even (often best!) as default for a role in a database:
ALTER ROLE foo IN DATABASE test SET search_path = blarg,public;
  1. Write the command at the top of your script. Or execute it in your DB session:
SET search_path = blarg,public;

psql 'host=myHost user=myUser dbname=myDb port=myPort options=--search_path=myschema'

or, if you prefer the URI style,

psql 'postgresql://myUser@myHost:myPort/myDb?options=--search_path%3dmyschema'

PGOPTIONS=--search_path=myschema psql -h myHost -U myUser -dmyDb -p myPort
  1. Set a specific search_path for the scope of a function (to be safe from malicious users with sufficient privileges). Read about Writing SECURITY 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 list trumps lower number.

To see the current setting:

SHOW search_path;

To reset it:

RESET search_path;

You can find configuration settings for roles and databases in the catalog table pg_db_role_setting.

SELECT r.rolname, d.datname, rs.setconfig
FROM   pg_db_role_setting rs
LEFT   JOIN pg_roles      r ON r.oid = rs.setrole
LEFT   JOIN pg_database   d ON d.oid = rs.setdatabase
WHERE  r.rolname = 'myrole' OR d.datname = 'mydb';

The next lower instance determines the default state of the search_path, which is postgresql.conf in this case or command-line options at server start

To unset any settings of a role or database - the search_path in this particular example:

ALTER ROLE myrole RESET search_path;

Or:

ALTER DATABASE mydb RESET search_path;

Or:

ALTER ROLE myrole in DATABASE mydb RESET search_path;

Essentially, the RESET command deletes a row from pg_db_role_setting