PostgreSQL Learning Handbook-Schema schema

Original: http://www.cnblogs.com/stephen-liu74/archive/2012/04/25/2291526.html

A database contains one or more named patterns, and the schema contains the tables. The schema also contains other named objects, including data types, functions, and operators. The same object name can be used in different modes without causing conflicts; For example, SCHEMA1 and MySchema can contain tables called mytable. Unlike databases, schemas are not strictly separated: a user can access an object in any mode in the database to which he is connected, as long as he has permissions.
There are several main reasons why we need the pattern:
1). Allows multiple users to use one database without interfering with other users.
2). Organize database objects into logical groups, making them easier to manage.
3). Third-party applications can be placed in different modes so that they do not conflict with the names of other objects.

1. Create a pattern:
CREATE SCHEMAMySchema;
The above command allows you to create a pattern named MySchema, which can have its own set of logical objects, such as tables, views, functions, and so on, after the pattern has been created.

2. Public mode:
Before we introduce the following, we need to explain the public mode first. Each time we create a new database, PostgreSQL automatically creates the schema for us. When you log in to the database, if there is no special designation, we will manipulate the various data objects in this mode (public), such as:
CREATE TABLEProducts (...) are equivalent toCREATE TABLEPublic.products (...)

3. Permissions:
By default, users cannot see objects in the pattern that do not belong to them. In order for them to see, the owner of the pattern needs to give the usage permission on the schema. In order for the user to use the object in the schema, we may need to give additional permissions as long as it is appropriate for that object. PostgreSQL provides different types of permissions based on different objects, such as:
GRANT all on SCHEMAMySchema toPublic
The all keyword above will containCREATEAndUSAGETwo kinds of permissions. If the public mode has the Create permission for MySchema mode, users logged into the mode can create arbitrary objects in the MySchema mode, such as:
CREATE TABLEMyschema.products (
Product_no Integer,
Name text,
Price numericCHECK(Price > 0),
);
When assigning permissions to all tables in a pattern, you need to split the permissions into a variety of table operations, such as:
ALTER DEFAULT Privileges in SCHEMAMySchema
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER on TABLES toPublic
When assigning permissions to all sequence sequence objects in a pattern, you need to split the permissions into a variety of sequence operations, such as:
ALTER DEFAULT Privileges in SCHEMAMySchema
GRANT SELECT, UPDATE, USAGE on sequences toPublic
When assigning permissions to all functions in a pattern, only execute permissions are considered, such as:
ALTER DEFAULT Privileges in SCHEMAMySchema
GRANT EXECUTE on FUNCTIONS toPublic
As you can see, it is extremely inconvenient to create various objects in the public mode for MySchema mode in the above way. Here we will introduce another way, that is, through the role object, directly login and associated to the MySchema object, then you can directly create a variety of objects in MySchema mode.
CREATE ROLEMySchemaLOGIN PASSWORD' 123456 '; --Creates a role object associated with the pattern.
CREATE SCHEMAMySchemaAUTHORIZATIONMySchema; --associates the pattern to the specified role, and the schema name and role name can be unequal.
Under the Linux shell, log in to the database mytest with the MySchema role and log on to the database successfully after the password is entered correctly.
/> psql-d mytest-u MySchema
Password:
mytest=> CREATE TABLE Test (I integer);
CREATE TABLE
Mytest=> \d--View a list of tables information that the mode has permission to see.
List of relations
Schema | Name |  Type | Owner
------------+---------+------+----------
MySchema | Test | Table | MySchema
(1 rows)

4. Delete mode:
DROP SCHEMAMySchema;
If you want to delete a pattern and all its objects, use cascade Delete:
DROP SCHEMAMySchemaCASCADE;

5. Mode Search Path:
When we use a database object, we can use its full name to locate the object, but this is often cumbersome, and each time we have to type Owner_name.object_name. PostgreSQL provides a pattern search path, which is similar to the $PATH environment variable in Linux, when we execute a shell command, only the command is located in the directory list of $path, we can execute directly by the command name, otherwise we need to enter its full path name. PostgreSQL also finds a search path to determine which table is a table, and this path is a list of patterns to look for. The first table found in the search path will be treated as a selected table. If there is no matching table in the search path, an error is reported, even if the name of the matching table exists in the other schema of the database.
The first pattern in a search path is called the current pattern. In addition to being the first mode of the search, it is the schema that the new table belongs to when the CREATE table does not have a schema name declared. To display the current search path, use the following command:
Mytest=>SHOW Search_path;
Search_path
----------------
"$user", public
(1 row)
The new schema can be added to the search path, such as:
SET Search_path toMyschema,public;
Sets the specified pattern for the search path, such as:
SET Search_path toMySchema; --The current search path will simply contain myschema a pattern.

PostgreSQL Learning Manual-Schema schema (RPM)