玩转PostgreSQL（二）：如何使用模式搜索路径

总结：在本文中您将学习PostgreSQL模式以及如何使用模式搜索路径并解析模式中的对象。

？什么是 PostgreSQL Schema

在 PostgreSQL 中，schema 是一个命名空间，其中包含称为数据库的对象，例如表、视图、索引、数据类型、函数、存储过程和标识符。

要访问模式中的对象，我们必须使用以下语法限定对象：

schema_name.object_name

一个数据库可以包含一个或多个模式，并且每个模式仅属于一个数据库。两个模式可以具有共享相同名称的不同对象。

例如，您可能有 测试架构、用户表、

SELECT * FROM test.user;

❿还有 ❿公共用户表。当引用 user 表时，您必须按如下方式对其进行限定：

public.user

还是

test.user

？为什么需要使用方案

？为什么需要使用模式：

• 模式允许我们将数据库对象（例如表）转换为逻辑组，以使其更易于管理。
• 架构允许多个用户使用数据库而不会互相干扰。

?public schema

对于每个新数据库，PostgreSQL 都会自动创建一个名为 public❿❿ 的 schema。无论我们创建的对象是否指定了模式名称，PostgreSQL 都会将其放置在 public 模式中。因此，以下陈述是否等效：

CREATE TABLE table_name(
  ...
);

和

CREATE TABLE public.table_name(
   ...
);

？模式搜索路径

实际上指的是没有模式名称的表，例如作为 test.user 表。

如果仅通过名称引用表，PostgreSQL 将使用 搜索路径 ，它是 模式❀​​的列表。

PostgreSQL 将访问模式搜索路径中的第一个匹配表。如果不匹配，即使该名称存在于数据库的另一个架构中，也会返回错误。

搜索路径中的第一个模式称为当前模式。请注意如果我们创建一个新对象而没有显式指定模式名称，PostgreSQL 也将使用新对象的当前模式。函数

current_schema() 返回当前模式：

SELECT current_schema();

输出

 current_schema
----------------
public
(1 row)

因此，对于我们创建的每个新对象，PostgreSQL 使用 public 修饰符。 A " $user" 指定 PostgreSQL 用于搜索与当前用户同名的对象的第一个方案。例如，如果我们使用 postgres 用户登录并访问 user 表。 PostgreSQL 在 postgres 架构中搜索 user 表。如果未找到此类对象，请继续在 public 架构中搜索 user 表。

要创建新方案，请使用 CREATE SCHEMA 说明：

CREATE SCHEMA test;

要向搜索路径添加新方案，请使用以下命令： ❀Name user 没有要指定模式名称，PostgreSQL 将其放在 user 表中 ♓❓❓Test❿❿ Test模式访问 user对于 表，我们可以使用以下之一语句使用：

SELECT * FROM user;

Or

SELECT * FROM test.user;

public schema 是搜索路径中的第二个元素，因此要访问 public schema 中的 er❿er❿ 表，我们的表名必须限定如下：

SELECT * FROM public.staff;

如果您使用模式，则必须明确引用public，那么您可以删除它

吗？ PostgreSQL 架构和权限

用户只能访问他们拥有的架构中的对象。这意味着他们无法接收模式中不属于他们的对象。

要允许用户访问他们不拥有的模式中的对象，我们必须 USAGE 授予模式权限

GRANT USAGE ON SCHEMA schema_name 
TO role_name;

要允许用户在他们没有的模式中创建对象，我们必须授予他们 CREATE架构权限：

GRANT CREATE ON SCHEMA schema_name 
TO user_name;

？请注意在 public 模式中，每个用户都有 CREATE 和 USAGE。

？ PostgreSQL 模式操作

• 要创建新模式，请使用 CREATE SCHEMA 声明。
• 要重命名架构或更改其所有者，请使用 ALTER SCHEMA 语句。
• 要删除架构，请使用 DELETE SCHEMA 语句。

在此之前，我们了解了 PostgreSQL 架构以及 PostgreSQL 如何使用搜索路径来解析对象名称。

？PostgreSQL SCHEMA CREATE 语句概述

？ 注意：这里我们将学习如何使用PostgreSQLCREATE SCHEMA语句在数据库中使用创建一个新的schema。

CREATE SCHEMA语句允许我们在当前数据库中创建一个新模式。

CREATE SCHEMA 语句：

CREATE SCHEMA [IF NOT EXISTS] schema_name;

在以下语法中：

• 首先，在 CREATE♿♿♿♿♿♿♿♿ 之后指定架构。模式名称在当前数据库中必须是唯一的。
• 其次，可以选择使用 IF NOT EXIST 仅在新架构不存在时有条件地创建新架构。尝试在不使用 IF NOT EXISTS 语句的情况下创建已存在的新架构将导致错误。

？ 注意：要执行CREATE SCHEMA语句，我们必须在当前数据库中有♓CREATE❝。

我们还可以为用户创建架构：

CREATE SCHEMA [IF NOT EXISTS] 
AUTHORIZATION username;

在本例中，用户名

的架构已创建，并且还允许我们创建对象语句和视图的表，如下所示：

CREATE SCHEMA schema_name
    CREATE TABLE table_name1 (...)
    CREATE TABLE table_name2 (...)
    CREATE VIEW view_name1
        SELECT select_list FROM table_name1;

？ 注意：请注意，每个子命令不以分号 (;) 结尾。

？PostgreSQL CREATE SCHEMA 示例

让我们举一些例子来理解♿♿♿♿♿♿♿。

?1) 使用 CREATE SCHEMA 创建新的 SCHEMA 示例

以下语句使用 CREATE♿ 名称 CREATE d 测试The以下语句返回当前数据库中的所有架构：

SELECT * 
FROM pg_catalog.pg_namespace
ORDER BY nspname;)

?2) 使用 CREATE SCHEMA♿ 为用户 gg- ?3) 使用 CREATE SCHEMA SCHEMA 创建架构及其对象的示例

以下示例使用 CREATE SCHEMA♓♓♓❿❿名称 ssm。它还创建一个名为 spring 的表和一个名为 spring_boot

CREATE SCHEMA ssm 
    CREATE TABLE spring(
        id SERIAL NOT NULL, 
        version DATE NOT NULL
    )
    CREATE VIEW spring_boot AS 
        SELECT ID, version
        FROM spring 
        WHERE version <= 2.2.0;

？ 注意：在上面的文章中，我们学习了如何使用 PostgreSQL CREATE SCHEMA 语句在数据库中创建新架构。

?PostgreSQL ALTER SCHEMA 语句概述

ALTER SCHEMA 语句允许我们更改模式的定义。例如，我们可以将模式重命名为：

ALTER SCHEMA schema_name 
RENAME TO new_name;

？ 注意:

• 首先，在 OLD SCHEMA 关键字后指定旧 schema_name。
• 其次，在 RENAME 关键字后指定新的 new_name。

请请注意，要执行此语句，我们必须是架构的所有者并拥有数据库权限CREATE。

除了重命名 schema 之外，ALTER SCHEMA 还允许我们将 schema 的所有者更改为新的所有者，如以下语句所示：

ALTER SCHEMA schema_name 
OWNER TO { new_owner | CURRENT_USER | SESSION_USER};

？ 注意 :

• 首先，ALTER SCHEMA 指定要更改所有者的模式的名称。
• 其次，所有者 输入新所有者。

？ PostgreSQL ALTER SCHEMA 语句示例

让我们给出一些使用 ALTER SCHEMA 的示例。

请请注意，以下部分中的示例基于我们所做的CREATE SCHEMA。

?1) 使用 OLD SCHEMA 重命名架构的示例

此示例使用 ♓AlTER SCHEMAOLD SCHEMA dd pp 的架构 的声明schema:

ALTER SCHEMA dd
RENAME TO pp;

类似地重命名以下示例 Testerschema:

ALTER SCHEMA tests
RENAME TO test;

?2) 使用

ALTER SCHEMA tests
RENAME TO test;

?2) 使用 ❿AL 语句更改架构所有者

或下面使用 的示例更改架构

ALTER SCHEMA dd 
OWNER TO postgres;

SELECT * 
FROM 
    pg_catalog.pg_namespace
WHERE 
    nspacl is NULL AND
    nspname NOT LIKE 'pg_%'
ORDER BY 
    nspname;

ALTER SCHEMA test 
OWNER TO postgres;

DROP SCHEMA [IF EXISTS] schema_name 
[ CASCADE | RESTRICT ];

DROP SCHEMA [IF EXISTS] schema_name1 [,schema_name2,...] 
[CASCADE | RESTRICT];

DROP SCHEMA IF EXISTS gg;

DROP SCHEMA IF EXISTS gg;

DROP SCHEMA IF EXISTS gg;

DROP SCHEMA IF EXISTS gg;

DROP SCHEMA IF EXISTS gg, test;

DROP SCHEMA ssm;

ERROR:  cannot drop schema scm because other objects depend on it
DETAIL:  table scm.deliveries depends on schema scm
view scm.delivery_due_list depends on schema scm
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
SQL state: 2BP01

DROP SCHEMA scm CASCADE;