PostgreSQL Schema -2

PRACTICAL 5.VIEW CREATION INTO THE SCHEMA

--Create a view from "benz2.dept" table
postgres=# create view dept_v as select * from benz2.dept;
CREATE VIEW

postgres=# select * from dept_v;
 dept_id | department 
---------+------------
    1011 | Research
    1012 | Sales
(2 rows)

postgres=# \dv
         List of relations
 Schema |  Name  | Type |  Owner   
--------+--------+------+----------
 public | dept_v | view | postgres
(1 row)

-- first grant the superuser privilege to "u2" user
postgres=# alter user u2 superuser;
ALTER ROLE

--connect as u2 user 
[postgres@r1 ~]$ psql -U u2 -d postgres
Password for user u2: 

--Create a schema and create a table and view within it as a super user
postgres=# CREATE SCHEMA books
    CREATE TABLE databases (title text, release date, rating text)
    CREATE VIEW postgres_view AS SELECT title, release FROM databases WHERE rating IS NOT NULL;
CREATE SCHEMA

--set the book search path for objects read
postgres=# set search_path to books;
SET
postgres=# show search_path;
 search_path 
-------------
 books
(1 row)

postgres=# \dv                      
          List of relations
 Schema |    Name     | Type | Owner 
--------+-------------+------+-------
 books  | postgres_view | view | u2
(1 row)

postgres=# \dt
         List of relations
 Schema |   Name    | Type  | Owner 
--------+-----------+-------+-------
 books  | databases | table | u2
(1 row)

PRACTICAL 6.CHANGING SEARCH_PATH

--Change the search path from "public" to "benz" First check the search path 
postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | school        | table | postgres
 public | school_bk     | table | postgres
 public | school_bk2    | table | postgres
 public | student       | table | postgres
 public | us_snail_addy | table | postgres
(5 rows)

--set  search_path as benz
postgres=# set search_path to benz;
SET

--see what search path you are currently using
postgres=# show search_path;
 search_path 
-------------
 benz
(1 row)

--list down benz schema's table
postgres=# \dt 
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 benz   | dept | table | postgres
 benz   | emp  | table | postgres
(2 rows)

--Now you can select emp table without pointing dot(benz.emp) but if you want 
see (public or benz2) another  schema table you need to specify schema name 
like "benz2.dept" 

postgres=# select * from emp; 
 dept_id | department 
---------+------------
       1 | nijam
       1 | nijam
       2 | john
(3 rows)

PRACTICAL 7.ASSIGN SEARCH_PATH PERMANANTLY AND PARTICULAR USER:

--List down the users
postgres-# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 john      | Superuser                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 u1        |                                                | {}
 u2        | Superuser                                      | {}
 u3        |                                                | {}
 u5        |                                                | {}
 u6        |                                                | {}
 u7        |                                                | {}
 u8        |  

--check the user u6 serach path after connected u6 user
postgres=> show search_path ;
  search_path   
----------------
 "$user",public
(1 row)

--connect as super user(u2)then assign search path permanently for a user,here 
we are assigning two search path (schema) to particular user u6
postgres=# alter user u6 set search_path to sara,benz2;
ALTER ROLE
postgres=# GRANT create ON SCHEMA sara TO u6; 
GRANT

It's from u6 user
postgres=# \q
[root@r1 bin]# ./psql -U u6 -d db2

postgres=> show search_path;
 search_path 
-------------
 sara, benz2
(1 row)


postgres=# \conninfo
You are connected to database "postgres" as user "u6" via socket in "/tmp" at port "5432".
postgres=# \q

--ReConnect  as "U6" user and check the search path correctly assigned or not
[postgres@r1 ~]$ psql -U u6 -d postgres
Password for user u6: 

postgres=> show search_path ;
 search_path 
-------------
 sara, benz2
(1 row)

postgres=> \dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 benz2   | postgres | postgres=UC/postgres+| 
         |          | u3=UC/postgres      +| 
         |          | u5=UC/postgres      +| 
         |          | u6=U/postgres       +| 
         |          | u7=UC/postgres       | 
 books   | u2       |                      | 
 public  | postgres | postgres=UC/postgres+| standard public schema
         |          | =U/postgres          | 
 sara    | u3       | u3=UC/u3            +| 
         |          | u5=C/u3             +| 
         |          | u6=C/u3              | 
 schema1 | u2       |                      | 
 schema2 | u2       |                      | 
 schema5 | postgres |                      | 
 schema6 | u2       |                      | 
(8 rows)

PRACTICAL 8.MULTIPLE SCHEMA ASSIGNING CURRENT USER:

--connect as u2 user using command line
[postgres@r1 ~]$ psql -U u2 -d postgres
Password for user u2: 

--check the search path of u2 user
postgres=# show search_path;
 search_path 
-------------
 books
(1 row)

--create  two schema names are schema,schema2
postgres=# create schema schema1;        
CREATE SCHEMA
postgres=# create schema schema2;
CREATE SCHEMA

--Now assigning multiple schema for current user
postgres=# set search_path to schema1,schema2;
SET

--Now u2 user got two schema's search path
postgres=# show search_path;
   search_path    
------------------
 schema1, schema2
(1 row)

--if u2 user search the table in the above schema the user did'nt see any 
table becouse schema1 and schema2 are new schema currently nobody did not 
create objects(tables,views...etc)

postgres=# \dt
No relations found.

Also, since schema1 is the first element in the path, new objects would by 
default be created in it