526互联

PostgreSql通过创建外部表而非dblink使访问速度提高,优化sql查询速度

发布时间 2023-06-19 16:03:04作者: Ideaway

resourceregister库

外部表创建

-- 导入插件
create extension postgres_fdw;
-- 创建服务名称为 operation 的服务 host为ip port为端口 dbname为数据库名称
create server operation foreign data wrapper postgres_fdw options (host 'localhost', port '5432', dbname 'operation');
-- 创建 mapping 
create user mapping for public server operation options (user 'postgres', password 'Szrzyj@123');


-- 创建外部表 schema_name为模式 table_name为表名称
create foreign table t_app_navigation (
navid			varchar	(50), 
sysid			varchar	(50),
nav_name		varchar	(200),
nav_code		varchar	(200),
nav_order		int4	,
url				varchar	(500),
pid				varchar	(50),
nav_description	varchar	(500),
nav_icon		text  
) server operation options (schema_name 'public', table_name 't_app_navigation');
-- 创建完成后直接查询查看是否可以查询到 判断创建是否成功
select *from t_app_navigation

create foreign table t_id_re_role_fun (
id	varchar		(50),
role_id	varchar	(50),
fun_id	varchar	(50),
opt_ids	varchar	(500)
) server operation options (schema_name 'public', table_name 't_id_re_role_fun');

create foreign table t_id_re_role_fun (
id	varchar		(50),
role_id	varchar	(50),
fun_id	varchar	(50),
opt_ids	varchar	(500)
) server operation options (schema_name 'public', table_name 't_id_re_role_fun');


create foreign table t_app_system (
id			varchar	(50),
name		varchar	(100),
sys_code	varchar	(50),
sys_order	numeric	,
url			varchar	(500),
remark		varchar	(500),
sys_gradecode	varchar	(255)

) server operation options (schema_name 'public', table_name 't_app_system');



create foreign table t_app_re_nav_fun (
id	varchar	(255),
navid	varchar	(255),
funid	varchar	(255)

) server operation options (schema_name 'public', table_name 't_app_re_nav_fun');


create foreign table t_app_function (
id			varchar	(50),
name		varchar	(200),
fun_code	varchar	(50),
fun_order	numeric	,
url			varchar	(500),
pid			varchar	(50),
opt_ids		varchar	(500),
remark		varchar	(500),
fun_icon	bytea	,
fun_type	varchar	(255)
) server operation options (schema_name 'public', table_name 't_app_function');

view_t_app_navigtion_auth 视图创建

 SELECT t.navid,
    t.sysid,
    t.nav_name,
    t.sys_code,
    t.nav_code,
    t.nav_order,
    t.nav_icon,
    t.nav_description,
    t.url,
    t.pid,
    t.user_id
   FROM ( SELECT n.navid,
            n.sysid,
            n.nav_name,
            s.sys_code,
            n.nav_code,
            n.nav_order,
            n.nav_icon,
            n.nav_description,
                CASE
                    WHEN ((n.url)::text = ''::text) THEN ff.url
                    ELSE n.url
                END AS url,
            n.pid,
            string_agg((r.user_id)::text, ','::text) AS user_id
           FROM (((((t_app_navigation n
             LEFT JOIN t_id_re_role_fun f ON (((n.navid)::text = (f.fun_id)::text)))
             LEFT JOIN t_id_re_user_role r ON (((r.role_id)::text = (f.role_id)::text)))
             LEFT JOIN t_app_system s ON (((s.id)::text = (n.sysid)::text)))
             LEFT JOIN t_app_re_nav_fun rf ON (((rf.navid)::text = (n.navid)::text)))
             LEFT JOIN t_app_function ff ON (((rf.funid)::text = (ff.id)::text)))
          GROUP BY n.navid, n.sysid, n.nav_name, s.sys_code, n.nav_code, n.nav_order, n.nav_description, n.nav_icon, n.url, ff.url, n.pid) t

view_t_app_role_resource 视图创建

 SELECT t.id,
    t.role_id,
    t.data_id,
    t.auth_scope,
    (t.invalid_time)::text AS invalid_time,
    t.user_id
   FROM ( SELECT a.id,
            a.role_id,
            a.data_id,
            a.auth_scope,
            a.invalid_time,
            string_agg((r.user_id)::text, ','::text) AS user_id
           FROM (t_re_role_resource a
             LEFT JOIN t_id_re_user_role r ON (((r.role_id)::text = (a.role_id)::text)))
          GROUP BY a.id, a.role_id, a.data_id, a.auth_scope, a.invalid_time) t