本文主要是介绍pgadmin4树节点增删查(三),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
二十九,架构
(一)查询
请求参数:
在这里插入代码片
pg模板:
在这里插入代码片
实际执行:
在这里插入代码片
(二)创建
各项权限定义:
{"nspacl": {"type": "SCHEMA","acl": ["C", "U"]},"deftblacl": {"type": "TABLE","acl": ["r", "a", "w", "d", "D", "x", "t"]},"defseqacl": {"type": "SEQUENCE","acl": ["U", "r", "w"]},"deffuncacl": {"type": "FUNCTION","acl": ["X"]},"deftypeacl": {"type": "TYPE","acl": ["U"]}
}
请求参数:
{'description': '11111', 'name': 'test1', 'namespaceowner': 'zhouyl','nspacl': [{'acltype': 'defaultacls', 'grantee': 'qingy','grantor': 'postgres', 'old_grantee': 'qingy','with_grant': ['ALL'], 'without_grant': []}]}
pg模板:
{% import 'macros/security.macros' as SECLABEL %}
{% import 'macros/privilege.macros' as PRIVILEGE %}
{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %}
{% if data.name %}
CREATE SCHEMA{% if add_not_exists_clause %} IF NOT EXISTS{% endif %} {{ conn|qtIdent(data.name) }}{% if data.namespaceowner %}AUTHORIZATION {{ conn|qtIdent(data.namespaceowner) }}{% endif %}{% endif %};
{# Alter the comment/description #}
{% if data.description %}COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}IS {{ data.description|qtLiteral }};
{% endif %}
{# ACL for the schema #}
{% if data.nspacl %}
{% for priv in data.nspacl %}{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}{% endfor %}
{% endif %}
{# Default privileges on tables #}
{% for defacl, type in [('deftblacl', 'TABLES'), ('defseqacl', 'SEQUENCES'),('deffuncacl', 'FUNCTIONS'), ('deftypeacl', 'TYPES')]
%}
{% if data[defacl] %}{% set acl = data[defacl] %}
{% for priv in acl %}{{ DEFAULT_PRIVILEGE.SET(conn, 'SCHEMA', data.name, type, priv.grantee,priv.without_grant, priv.with_grant, priv.grantor) }}{% endfor %}
{% endif %}
{% endfor %}
{# Security Labels on schema #}
{% if data.seclabels and data.seclabels|length > 0 %}
{% for r in data.seclabels %}{{ SECLABEL.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }}
{% endfor %}
{% endif %}
实际执行:
CREATE SCHEMA test1 AUTHORIZATION zhouyl;
COMMENT ON SCHEMA test1 IS '11111';
GRANT ALL ON SCHEMA test1 TO qingy WITH GRANT OPTION;
创建完成后执行下面语句获取 oid:
SELECT nsp.oid FROM pg_catalog.pg_namespace nsp WHERE nsp.nspname = 'test1';
(三)属性
请求参数:
gid=1
sid=2
did=36121
scid=43220
pg模板:
SELECTCASEWHEN (nspname LIKE E'pg\\_temp\\_%') THEN 1WHEN (nspname LIKE E'pg\\_%') THEN 0ELSE 3 END AS nsptyp,nsp.nspname AS name,nsp.oid,pg_catalog.array_to_string(nsp.nspacl::text[], ', ') as acl,r.rolname AS namespaceowner, description,pg_catalog.has_schema_privilege(nsp.oid, 'CREATE') AS can_create,{### Default ACL for Tables ###}(SELECT pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.array_to_string(defaclacl::text[], ', ')FROM pg_catalog.pg_default_aclWHERE defaclobjtype = 'r' AND defaclnamespace = nsp.oid), ', ')) AS tblacl,{### Default ACL for Sequnces ###}(SELECT pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.array_to_string(defaclacl::text[], ', ')FROM pg_catalog.pg_default_aclWHERE defaclobjtype = 'S' AND defaclnamespace = nsp.oid), ', ')) AS seqacl,{### Default ACL for Functions ###}(SELECT pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.array_to_string(defaclacl::text[], ', ')FROM pg_catalog.pg_default_aclWHERE defaclobjtype = 'f' AND defaclnamespace = nsp.oid), ', ')) AS funcacl,{### Default ACL for Type ###}(SELECT pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.array_to_string(defaclacl::text[], ', ')FROM pg_catalog.pg_default_aclWHERE defaclobjtype = 'T' AND defaclnamespace = nsp.oid), ', ')) AS typeacl,(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=nsp.oid) AS seclabels
FROMpg_catalog.pg_namespace nspLEFT OUTER JOIN pg_catalog.pg_description des ON(des.objoid=nsp.oid AND des.classoid='pg_namespace'::regclass)LEFT JOIN pg_catalog.pg_roles r ON (r.oid = nsp.nspowner)
WHERE{% if scid %}nsp.oid={{scid}}::oid AND{% else %}{% if not show_sysobj %}nspname NOT LIKE E'pg\\_%' AND{% endif %}{% endif %}NOT ((nsp.nspname = 'pg_catalog' AND EXISTS(SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'pg_class' ANDrelnamespace = {{ tbl }}.oid LIMIT 1)) OR(nsp.nspname = 'pgagent' AND EXISTS(SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'pga_job' ANDrelnamespace = {{ tbl }}.oid LIMIT 1)) OR(nsp.nspname = 'information_schema' AND EXISTS(SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'tables' ANDrelnamespace = {{ tbl }}.oid LIMIT 1))){% if schema_restrictions %}ANDnsp.nspname in ({{schema_restrictions}}){% endif %}
ORDER BY 1, nspname;
实际执行:
SELECTCASEWHEN (nspname LIKE E'pg\\_temp\\_%') THEN 1WHEN (nspname LIKE E'pg\\_%') THEN 0ELSE 3 END AS nsptyp,nsp.nspname AS name,nsp.oid,pg_catalog.array_to_string(nsp.nspacl::text[], ', ') as acl,r.rolname AS namespaceowner, description,pg_catalog.has_schema_privilege(nsp.oid, 'CREATE') AS can_create,(SELECT pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.array_to_string(defaclacl::text[], ', ')FROM pg_catalog.pg_default_aclWHERE defaclobjtype = 'r' AND defaclnamespace = nsp.oid), ', ')) AS tblacl,(SELECT pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.array_to_string(defaclacl::text[], ', ')FROM pg_catalog.pg_default_aclWHERE defaclobjtype = 'S' AND defaclnamespace = nsp.oid), ', ')) AS seqacl,(SELECT pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.array_to_string(defaclacl::text[], ', ')FROM pg_catalog.pg_default_aclWHERE defaclobjtype = 'f' AND defaclnamespace = nsp.oid), ', ')) AS funcacl,(SELECT pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.array_to_string(defaclacl::text[], ', ')FROM pg_catalog.pg_default_aclWHERE defaclobjtype = 'T' AND defaclnamespace = nsp.oid), ', ')) AS typeacl,(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=nsp.oid) AS seclabels
FROMpg_catalog.pg_namespace nspLEFT OUTER JOIN pg_catalog.pg_description des ON(des.objoid=nsp.oid AND des.classoid='pg_namespace'::regclass)LEFT JOIN pg_catalog.pg_roles r ON (r.oid = nsp.nspowner)
WHEREnsp.oid=43220::oid ANDNOT ((nsp.nspname = 'pg_catalog' AND EXISTS(SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'pg_class' ANDrelnamespace = nsp.oid LIMIT 1)) OR(nsp.nspname = 'pgagent' AND EXISTS(SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'pga_job' ANDrelnamespace = nsp.oid LIMIT 1)) OR(nsp.nspname = 'information_schema' AND EXISTS(SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'tables' ANDrelnamespace = nsp.oid LIMIT 1)))ORDER BY 1, nspname;
(四)删除
请求参数:
gid=1
sid=2
did=36121
scid=43220
pg模板:
1,先根据 oid 查出名称
SELECT nsp.nspname FROM pg_catalog.pg_namespace nsp WHERE nsp.oid = {{ scid|qtLiteral }};2,在根据名称删除
DROP SCHEMA IF EXISTS {{ conn|qtIdent(name) }} {% if cascade %}CASCADE{%endif%};
实际执行:
1,查询
SELECT nsp.nspname FROM pg_catalog.pg_namespace nsp WHERE nsp.oid = 43220;2,删除
DROP SCHEMA IF EXISTS test1;
----------------------------------------------------------------------------------
请求参数:
在这里插入代码片
pg模板:
在这里插入代码片
实际执行:
在这里插入代码片
(一)查询
请求参数:
在这里插入代码片
pg模板:
在这里插入代码片
实际执行:
在这里插入代码片
(二)创建
请求参数:
在这里插入代码片
pg模板:
在这里插入代码片
实际执行:
在这里插入代码片
(三)属性
请求参数:
在这里插入代码片
pg模板:
在这里插入代码片
实际执行:
在这里插入代码片
(四)删除
请求参数:
在这里插入代码片
pg模板:
在这里插入代码片
实际执行:
在这里插入代码片
(五)
(六)
(七)
(八)
(九)
(十)
(十一)
1,标签
2,标签
3,标签
4,标签
5,标签
6,
7,
8,
(1)
(2)
(3)
(4)
(5)
(6)
(7)
(8)
附录
(一)创建与使用事件触发器
准备测试对象:
1,创建数据表:
CREATE TABLE ddl_history (id serial primary key,ddl_date timestamptz,ddl_tag text,object_name text
);
2,创建事件触发器函数:
CREATE OR REPLACE FUNCTION log_ddl()RETURNS event_trigger AS $$
DECLAREaudit_query TEXT;r RECORD;
BEGINIF tg_tag <> 'DROP TABLE'THENr := pg_event_trigger_ddl_commands();INSERT INTO ddl_history (ddl_date, ddl_tag, object_name) VALUES (statement_timestamp(), tg_tag, r.object_identity);END IF;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION log_ddl_drop()RETURNS event_trigger AS $$
DECLAREaudit_query TEXT;r RECORD;
BEGINIF tg_tag = 'DROP TABLE'THENFOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOPINSERT INTO ddl_history (ddl_date, ddl_tag, object_name) VALUES (statement_timestamp(), tg_tag, r.object_identity);END LOOP;END IF;
END;
$$ LANGUAGE plpgsql;
3,创建事件触发器:
CREATE EVENT TRIGGER log_ddl_info ON ddl_command_end EXECUTE PROCEDURE log_ddl();CREATE EVENT TRIGGER log_ddl_drop_info ON sql_drop EXECUTE PROCEDURE log_ddl_drop();
4,执行一些DDL操作:
postgres=# CREATE TABLE testtable (id int, first_name text);
CREATE TABLE
postgres=# ALTER TABLE testtable ADD COLUMN last_name text;
ALTER TABLE
postgres=# ALTER TABLE testtable ADD COLUMN midlname text;
ALTER TABLE
postgres=# ALTER TABLE testtable RENAME COLUMN midlname TO middle_name;
ALTER TABLE
postgres=# ALTER TABLE testtable DROP COLUMN middle_name;
ALTER TABLE
postgres=# DROP TABLE testtable;
DROP TABLE
postgres=# select * from ddl_history;
(二)创建与使用postgresql_fdw
1,在本地数据库创建postgresql_fwd扩展:
创建结果:
刷新“外部数据封装器”节点,得到:
2,在本地创建外部服务器:
创建完成后,就创建了远程服务器连接信息:
3,在本地创建用户映射:
创建完成后,就实现了本地用户到远程用户的映射:
4,在本地schema下创建外部表:
- 在定义列时,数据类型和其他属性必须与实际的远程表一致。
创建完成后,完成本地表对远程表的映射:
5,像操作本地表一样操作远程表
(三)使用视图
1,创建两个表:
CREATE TABLE teacher (id int NOT NULL,sname varchar(100)
);CREATE TABLE student (sid int NOT NULL,teacher_id int NOT NULL DEFAULT 0,tname varchar(100)
);
2,插入一些数据:
INSERT INTO public.teacher VALUES (1,'sname1');
INSERT INTO public.teacher VALUES (2,'sname2');
INSERT INTO public.teacher VALUES (3,'sname3');INSERT INTO public.student VALUES (1,1,'tname1');
INSERT INTO public.student VALUES (2,1,'tname1');
INSERT INTO public.student VALUES (3,2,'tname2');
INSERT INTO public.student VALUES (4,3,'tname3');
INSERT INTO public.student VALUES (5,3,'tname3');
3,创建视图,从表中收集数据:
CREATE OR REPLACE VIEW student_view AS
SELECT *FROM studentLEFT JOIN teacher ON student.teacher_id = teacher.id;
4,使用视图:
SELECT * FROM student_view;
(四)使用物化视图
1,使用上面创建的表和数据。
2,创建物化视图:
CREATE MATERIALIZED VIEW student_view_m AS
SELECT *FROM studentLEFT JOIN teacher ON student.teacher_id = teacher.id;
3,向物化视图填充数据:
psql$ REFRESH MATERIALIZED VIEW
4,使用物化视图:
SELECT * FROM student_view_m;
(五)创建与使用触发器
1,创建一张表:
CREATE TABLE IF NOT EXISTS public.orders
(id integer NOT NULL DEFAULT nextval('orders_id_seq'::regclass),order_date date,total_amount numeric(10,2),status character varying(20) COLLATE pg_catalog."default",CONSTRAINT orders_pkey PRIMARY KEY (id)
)
2,创建触发器函数:
CREATE OR REPLACE FUNCTION update_order_status()
RETURNS TRIGGER AS $update_order_status_trigger$
BEGINIF NEW.total_amount > 1000 THENNEW.status := '已审核';ELSENEW.status := '待审核';END IF;RETURN NEW;
END;
$update_order_status_trigger$ LANGUAGE plpgsql;
3,创建触发器:
CREATE TRIGGER update_order_status_trigger
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION update_order_status();
4,向表中插入数据:
INSERT INTO orders (order_date, total_amount) VALUES ('2022-01-01', 1500);
INSERT INTO orders (order_date, total_amount) VALUES ('2022-01-02', 500);
5,查看表内容:
SELECT * FROM public.orders
ORDER BY id ASC
(六)表中的排他约束
5.4.6.排他约束
PostgreSQL exclude排它约束详解
1,创建一张表:
CREATE TABLE circles
( c circle,EXCLUDE USING gist (c WITH &&)
);
2,创建 btree_gist 扩展:
CREATE EXTENSION btree_gistSCHEMA publicVERSION "1.6";
3,创建排他表:
CREATE TABLE t2
( c1 INTEGER, c2 TEXT, EXCLUDE USING GIST (c1 WITH =, c2 WITH <>)
);
4,向表中插入数据:
# 第一次执行
insert into t2 values(1,'a');# 第二次执行
insert into t2 values(1,'a');# 第三次执行,回报错
ERROR: conflicting key value violates exclusion constraint "t2_c1_c2_excl"
DETAIL: Key (c1, c2)=(1, b) conflicts with existing key (c1, c2)=(1, a).
SQL 状态: 23P01
- 当 c1 列的值确定为 1 时,只能插入 c2 为 ’a’ 的值,插入其它数据则会报错。
(七)获取各类树节点 OID 的SQL
1,数据库 OID
SELECT oid FROM pg_database WHERE datname = '数据库名称';
2,获取所有登录/组角色 OID
SELECT oid, rolname FROM pg_roles
3,表空间
SELECT oid, spcname AS name FROM pg_tablespace;
4,publication
SELECT oid, pubname AS name
FROM pg_publication;
5,subscription
SELECT oid, subname AS name
FROM pg_subscription;
6,事件触发器
SELECT oid, evtname AS name
FROM pg_event_trigger;
7,外部服务器
SELECT oid, evtname AS name
FROM pg_event_trigger;
8,用户映射
SELECT umid, srvname AS name
FROM pg_user_mappings;
9,强制转换
SELECT castsource::regtype::oid AS source_oid,casttarget::regtype::oid AS target_oid
FROM pg_cast;SELECT castsource::regtype::oid AS source_oid,casttarget::regtype::oid AS target_oid
FROM pg_cast
WHERE castsource = '源类型'::regtypeAND casttarget = '目标类型'::regtype;
10,扩展
SELECT oid, extname AS name
FROM pg_extension;
11,schema
SELECT oid, nspname AS name
FROM pg_namespace;
12,table
方式一:通过 schema OID 和 table 名称来查 table OID
SELECT rel.oid as tid
FROM pg_catalog.pg_class rel
WHERE rel.relkind IN ('r','s','t','p')
AND rel.relnamespace = {{ sci d }}::oid
AND rel.relname = {{ tableName }}
方式二:通过 schema 名称和 table 名称来查 table OID
SELECT * FROM pg_class WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = {{ schemaName }}) AND relname = {{ tableName }};
13,sequence
根据名称查询sequence oid的SQL:
SELECT oid FROM pg_class WHERE relname = 'sequence_name' AND relkind = 'S';
14,触发器
根据触发器名称查询oid:
SELECT oid FROM pg_trigger WHERE tgname = 'trigger_name';
数据库拓展(extension)属性数据 √
登录/组角色(resource_group/role-p)
数据库语言(language)属性 √
view_rules
view_triggerTable---- Constraint---下面五个 √
foreign_table
trigger_functions √
Function √
Materialized view √
这篇关于pgadmin4树节点增删查(三)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!