jiang demo1

(1) 查询表的模式名和表名
--查询系统试图 pg_tables
select tablename,schemaname from pg_tables where tablename='p_table';
(2) 查询表的所在节点 nodeoids 信息
select t1.relname,t2.nodeoids 
from pg_class t1,pgxc_class t2, pg_namespace t3 
where t1.oid=t2.pcrelid 
and t1.relnamespace=t3.oid
and t1.relname='p_table' 
and t3.nspname='root'; 
(3) 查询表所在的实例信息
select t4.* 
from pg_class t1,pgxc_class t2,pg_namespace t3, pgxc_node t4 
where t1.oid=t2.pcrelid 
and t1.relnamespace=t3.oid
and cast(t2.nodeoids as varchar(20))=cast(t4.oid as varchar(20))
and t1.relname='p_table' and t3.nspname='omm';
(4)将表 sjh_test 表的读取、删除权限授权给 sjh111 用户
----普通用户只有 public 模式的权限，需要将当前 schema 使用权限赋予给用户
grant usage on schema root to sjh111; --赋权
grant select ,delete on sjh_test to sjh111;
(5)为用户 sjh111 授权在 sjh_test 表的 a,b 列上的查询、添加和更新权限
grant select(a,b),insert(a,b),update(a,b) on sjh_test to sjh111;
(6)将用户 sjh111 授权在 sjh_test 表的 a 列上的查询、添加和更新权限回收
revoke select(a),insert(a),update(a) on sjh_test from sjh111;
(7)创建角色 sjh_audit ，此角色拥有审计权限
create role sjh_audit1 with auditadmin password "test@123";
(8)将 sjh112 角色授权于用户 sjh111,并运行 sjh111 继承权限可以再次授予其他角色或用户
grant sjh112 to sjh111 with admin option;
(9)创建新用户 sjh113,设置使用有效期"2023-01-28" 至"2026-01-01"
create user sjh113 password "test@123" valid begin "2023-01-28" valid until "2026-01-01";
(10)打开 all_data 表的行级访问控制策略开关
alter table all_data enable row level security;
(11)创建行级访问控制策略，当前用户只能查看用户自身的数据
create row level security  policy rls01 on all_data using(role=current_user);
(12)删除策略
drop row level security policy rls01 on all_data;
(13)关闭行级访问控制
alter table all_data disable row level security;
(14) 创建试图 SELECT_SD,查询学生成绩信息，查询学生姓名，课程名称，课程成绩
create or replace view SELECT_SD as select sname,cname,grade 
from student s, course c,elective e 
where e.sno=s.sno and e.cno=c.cno;
(15) 编写函数 FUNC_SUM,返回某个学生的分数总和
create or replace function FUNC_SUM(stuid int) returns integer as
$$
declare result integer;
begin
select sum(grade) into result from elective where sno=stuid;
return result;
end;
$$language plpgsql;
(16)创建触发器 DELETE_ELE,在 student 表上绑定触发器 DELETE_ELE,在删除表中某个学生时,将 ELECTIVE 表中该学生的选课记录一并删除;
create or replace function func_01() returns trigger as
$$
begin
delete from elective where sno =old.sno;
return old;
end;
$$language plpgsql; 
(17)创建触发器 DELETE_ELE
create or replace trigger DELETE_ELE before delete on student for each row execute procedure func_01();