Should the schema always be explicitly defined in the SQL statement?

Question

Earlier I had asked the question:

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

The answer I accepted was to modify the search_path for the connecting user so that the schema need not be specified in the SQL. However, now I wonder if I should always specify the schema in SQL rather than allow the schema to be automatically inferred by the search path. This seems like it would be a safer approach and would be more portable to other databases.

This question is different than the previous one in that I want to know what the best practices are for defining the schema in SQL, rather than how it can be done.

Should the schema always be explicitly defined in the SQL statement?

** Note: I would not hard code the schema name but would allow it to be configurable through the Web.config file so that the schema could change from one installation to another. **

Answer 1

It's a bad practice to hardcode schema into SQL statements.

You should keep it in the application settings and issue SET search_path after connecting to the database.

If your application is used by multiple users with their own schemas, your life will be much easier if you don't hardcode schema name into SQL.

In other words,

string query = "SELECT * FROM " + ConfigurationManager.AppSettings.Get("schema") + ".table";

is a bad way;

SQLCommand("SET search_path = " + ConfigurationManager.AppSettings.Get("schema"), connection).ExecuteNonQuery();
string query = "SELECT * FROM table";

is a good way.

Answer 2

Let's see - in the DB of the app I maintain there are around a dozen schemas. What would be the order if I put them in "search_path"? And would I put in the schema names (not the tables name and not the fully-qualified table names) in the configuration?

As you have guessed by now I do not use "search_path". But maybe you could store the fully-qualified table names in the configuration in case you ever change you mind about the names of the schemas or the tables themselves.