在数据库中创建完成数据后可以完成以下内容。
一、存储过程调用
CREATE OR REPLACE FUNCTION func()
RETURNS table(num bigint,mon money,dat date)
AS $$
BEGIN
return query
SELECT * FROM
Pro_CurrentSale('2019-01-01');
END
$$ LANGUAGE plpgsql;
二、使用触发器更新视图结果
CREATE OR REPLACE FUNCTION moneychange()
RETURNS TRIGGER
AS $$
BEGIN
IF(TG_OP='UPDATE')
THEN
UPDATE book
set price=new.价格
from book_au,author
WHERE book.title=old.书名
and author.au_id=book_au.au_id
and book_au.isbn=book.isbn
and author.au_lname=old.作者;
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ language plpgsql;
CREATE TRIGGER Tri_InsertSale
INSTEAD OF UPDATE
ON lookup
FOR EACH ROW EXECUTE PROCEDURE
moneychange();
--更新lookup视图
UPDATE lookup
SET 价格='1.00'
WHERE 书名='永得他得就子国家' and 作者='臧维花';
三、创建用户权限语句结果
CREATE ROLE "R_Customer" WITH
LOGIN --登陆权限
NOSUPERUSER --不是超级用户
NOCREATEDB --没有创建数据库权限
NOCREATEROLE --没有创建角色权限
INHERIT --具有继承父角色的权限
NOREPLICATION --没有复制权限
CONNECTION LIMIT -1;
GRANT SELECT,INSERT,UPDATE
ON BOOK TO "R_Customer";
CREATE ROLE "R_Seller" WITH
LOGIN --登陆权限
SUPERUSER --是超级用户
CREATEDB --创建数据库权限
CREATEROLE --创建角色权限
INHERIT --具有继承父角色的权限
REPLICATION --有复制权限
CONNECTION LIMIT -1;
GRANT
SELECT,INSERT,UPDATE,DELETE ON BOOK TO "R_Seller";
GRANT
SELECT,INSERT,UPDATE,DELETE ON BOOK_au TO "R_Seller";
GRANT
SELECT,INSERT,UPDATE,DELETE ON author TO "R_Seller";
GRANT SELECT,INSERT,UPDATE,DELETE
ON publisher TO "R_Seller";
四、创建用户语句结果
CREATE USER U_Customer;
GRANT "R_Customer" TO U_Customer;
CREATE USER U_Seller;
GRANT "R_Seller" TO U_Seller;