Databases
StackOverflow2013
Postsdbo
POWhere (or how) should I define the schema in a select statement when using PostgreSQL?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhere (or how) should I define the schema in a select statement when using PostgreSQL?
    text
    copied!<p>I have an application built upon ASP.NET 3.5 and PostgreSQL 8.3. My database has 3 users, a data owner (xxx-owner), a data editor (xxx-editor), and a data viewer (xxx-viewer). Each user has their own schema called xxx-schema, xxx-editor-schema and xxx-viewer-schema. All the data is stored in xxx-schema. </p> <p>This requires that I specify the schema when connecting to the database as either the xxx-editor or xxx-viewer users as shown in the following snippet.</p> <pre><code>NpgsqlCommand pgCommand = new NpgsqlCommand(); pgCommand.CommandText = @"SELECT bldg_id, bldg_name, bldg_no, FROM " + this.schema + @".buildings WHERE UPPER(bldg_id) LIKE UPPER(@id);"; pgCommand.Parameters.Add("@id", "%" + id + "%"); pgCommand.Connection = pgConnection; </code></pre> <p>If I were to follow this route I would specify the schema in the Web.config. However, there must be a better way to specify which schema should be used. I would like to be able to remove the string concatenation entirely if possible. </p> <p>What is the best way to define the schema to be used in the SQL statement?</p> <hr> <p><em>EDIT</em></p> <p>The following from the <a href="http://npgsql.projects.postgresql.org/docs/manual/UserManual.html" rel="nofollow noreferrer">PostgreSQL User Manual</a> looks promising.</p> <blockquote> <p>SearchPath - Changes search path to specified and public schemas.</p> </blockquote> <p>From my limited testing it works alright in my situation. However, this is a PostgreSQL only solution. Is there perhaps a more database agnostic solution, do most databases provided such functionality?</p> <hr> <p><em>EDIT 2</em></p> <p>I've marked the answer below which lead me to changing the schema lookup order at the database level. With the following change I do not need to add code in my SQL statements.</p> <pre><code>ALTER USER xxx-viewer SET search_path TO '$user', xxx, public, sde </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload