indicator_views数据表上有一个触发器,从新增记录中提取视图定义语句,然后调用EXECUTE语句在userviews模式创建视图,对应的存储过程如下:
`
CREATE OR REPLACE FUNCTION public.create_indicator_view()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
view_name TEXT;
is_exist BOOL;
BEGIN
view_name := format('%s', NEW.name);
SELECT EXISTS (SELECT * FROM pg_catalog.pg_views WHERE schemaname = 'userviews' AND viewname = view_name) INTO is_exist;
IF NOT is_exist THEN
EXECUTE format(NEW.definition);
END IF;
RETURN NULL;
END
$function$
`
在userviews模式下为example用户服务访问视图的权限
ALTER DEFAULT PRIVILEGES IN SCHEMA userviews GRANT SELECT ON TABLES TO example; GRANT USAGE ON SCHEMA userviews TO example; GRANT SELECT ON ALL TABLES IN SCHEMA userviews TO example;
indicator_views数据表上有一个触发器,从新增记录中提取视图定义语句,然后调用EXECUTE语句在userviews模式创建视图,对应的存储过程如下: ` CREATE OR REPLACE FUNCTION public.create_indicator_view() RETURNS trigger LANGUAGE plpgsql AS $function$ DECLARE view_name TEXT; is_exist BOOL; BEGIN view_name := format('%s', NEW.name);
END $function$ `
ALTER DEFAULT PRIVILEGES IN SCHEMA userviews GRANT SELECT ON TABLES TO example; GRANT USAGE ON SCHEMA userviews TO example; GRANT SELECT ON ALL TABLES IN SCHEMA userviews TO example;