定义

视图就是由查询语句定义的虚拟表。视图默认是只读的

为什么使用视图：

• 可使复杂的查询易于理解和使用
• 安全原因，视图可以隐藏一些数据，如在一张用户表中，可以通过定义视图把密码等敏感字段去掉
• 把一些函数返回的结果映射成视图

创建视图

排除user表中的password字段

create table users (
  id int,
  user_name varchar(40),
  password varchar(256),
  user_email text,
  user_mark text
);

create view vm_users as select id, user_name, user_email, user_mark from users;

temp创建临时视图，session结束消失

create temp view vm_users as select id, user_name, user_email, user_mark from users;

视图重定义列名

create view vm_users(no, name, email, mark) as select id, user_name, user_email, user_mark from users;

可更新视图

通过创建触发器实现

# 触发器-插入函数
create or replace function vm_users_insert_trigger()
returns trigger as
$$
begin 
  insert into users values(NEW.id, NEW.user_name, '111111', NEW.user_email, NEW.user_mark);
  return null;
end;
$$
language plpgsql;

# 触发器 - 更新函数
create or replace function vm_users_update_trigger()
returns trigger as
$$
begin
  update users set user_email = NEW.user_email where id = NEW.id;
  return null;
end;
$$
language plpgsql;

# 触发器 - 删除函数
create or replace function vm_users_delete_trigger()
returns trigger as
$$
begin
  delete from users where id = NEW.id;
  return null;
end;
$$
language plpgsql;


# 触发器 - 插入
create trigger insert_vm_users_trigger
  instead of insert on vm_users
  for each row execute procedure vm_users_insert_trigger();

# 触发器 - 更新
create trigger update_vm_users_trigger
  instead of update on vm_users
  for each row execute procedure vm_users_update_trigger();

# 触发器 - 删除
create trigger delete_vm_users_trigger
  instead of delete on vm_users
  for each row execute procedure vm_users_delete_trigger();