使用PostgreSQL时，应该在select语句中的何处（或如何）定义模式？

问题描述

我有一个基于ASP.NET 3.5和PostgreSQL 8.3的应用程序。我的数据库有3个用户，一个数据所有者（xxx-owner），一个数据编辑器（xxx-editor）和一个数据查看器（xxx-viewer）。每个用户都有自己的架构，分别称为xxx模式，xxx编辑器模式和xxx查看者模式。所有数据都存储在xxx模式中。

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.

这要求我在连接数据库时以xxx编辑器或xxx查看器用户的身份指定架构，如以下代码片段所示。

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;

如果要遵循此路线，我将在Web.config中指定架构。但是，必须有一种更好的方法来指定应使用的架构。我希望能够在可能的情况下完全删除字符串连接。

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.

定义在SQL语句中使用的架构的最佳方法是什么？

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

EDIT

来自看起来很有希望。

根据我的有限测试，它在我的情况下可以正常工作。但是，这是仅PostgreSQL的解决方案。也许还有更多与数据库无关的解决方案，大多数数据库是否提供了这样的功能？

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

我在下面标记了答案，这使我在数据库级别更改了模式查找顺序。进行以下更改后，我无需在SQL语句中添加代码。

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

推荐答案

您可以设置每个会话使用以下模式搜索路径：

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

这篇关于使用PostgreSQL时，应该在select语句中的何处（或如何）定义模式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！