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模板：

SELECT
    CASE
    WHEN (nspname LIKE E'pg\\_temp\\_%') THEN 1
    WHEN (nspname LIKE E'pg\\_%') THEN 0
    ELSE 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_acl
        WHERE 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_acl
        WHERE 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_acl
        WHERE 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_acl
        WHERE 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
FROM
    pg_catalog.pg_namespace nsp
    LEFT 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' AND
            relnamespace = {{ tbl }}.oid LIMIT 1)) OR
    (nsp.nspname = 'pgagent' AND EXISTS
        (SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'pga_job' AND
            relnamespace = {{ tbl }}.oid LIMIT 1)) OR
    (nsp.nspname = 'information_schema' AND EXISTS
        (SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'tables' AND
            relnamespace = {{ tbl }}.oid LIMIT 1))
    )
    {% if schema_restrictions %}
        AND
        nsp.nspname in ({{schema_restrictions}})
    {% endif %}
ORDER BY 1, nspname;

实际执行：

SELECT
    CASE
    WHEN (nspname LIKE E'pg\\_temp\\_%') THEN 1
    WHEN (nspname LIKE E'pg\\_%') THEN 0
    ELSE 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_acl
        WHERE 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_acl
        WHERE 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_acl
        WHERE 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_acl
        WHERE 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
FROM
    pg_catalog.pg_namespace nsp
    LEFT 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
        nsp.oid=43220::oid AND
        NOT (
		(nsp.nspname = 'pg_catalog' AND EXISTS
        (SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'pg_class' AND
            relnamespace = nsp.oid LIMIT 1)) OR
    (nsp.nspname = 'pgagent' AND EXISTS
        (SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'pga_job' AND
            relnamespace = nsp.oid LIMIT 1)) OR
    (nsp.nspname = 'information_schema' AND EXISTS
        (SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'tables' AND
            relnamespace = 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 $$
DECLARE
  audit_query TEXT;
  r RECORD;
BEGIN
  IF tg_tag <> 'DROP TABLE'
  THEN
    r := 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 $$
DECLARE
  audit_query TEXT;
  r RECORD;
BEGIN
  IF tg_tag = 'DROP TABLE'
  THEN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() 
    LOOP
      INSERT 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 student
   LEFT JOIN teacher 
   ON student.teacher_id = teacher.id;

4，使用视图：

SELECT * FROM student_view;

（四）使用物化视图

1，使用上面创建的表和数据。
2，创建物化视图：

CREATE MATERIALIZED VIEW student_view_m AS
SELECT  *
   FROM student
   LEFT 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$
BEGIN
    IF NEW.total_amount > 1000 THEN
        NEW.status := '已审核';
    ELSE
        NEW.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_gist
    SCHEMA public
    VERSION "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 = '源类型'::regtype
  AND 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_trigger
 Table---- Constraint---下面五个   √
foreign_table 
trigger_functions  √
Function  √
Materialized view  √