Where (or how) should I define the schema in a select statement when using PostgreSQL?

Question

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.

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.

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;

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.

What is the best way to define the schema to be used in the SQL statement?

---

EDIT

The following from the PostgreSQL User Manual looks promising.

SearchPath - Changes search path to specified and public schemas.

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?

---

EDIT 2

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.

ALTER USER xxx-viewer SET search_path TO '$user', xxx, public, sde

Answer 1

You can set the schema search path on a per-session basis using:

SET search_path TO myschema

If you want to fall back to the public schema, you could use:

SET search_path TO myschema, public