SELECT [ALL | DISTINCT [ON (expression [, …])]]
* | expression [[AS] output_name] [, …]
[FROM from_item [, …]]
[WHERE condition]
[GROUP BY grouping_element [, …]]
[HAVING condition [, …]]
[WINDOW window_name AS (window_definition) [, …]]
[{UNION | INTERSECT | EXCEPT} [ALL | DISTINCT] select]
[ORDER BY expression [ASC | DESC | USING operator] [NULLS {FIRST | LAST}] [, …]]
[LIMIT {count | ALL}]
[OFFSET start [ROW | ROWS]]
[FETCH {FIRST | NEXT} [count] {ROW | ROWS} {ONLY | WITH TIES}]
[FOR {UPDATE | NO KEY UPDATE | SHARE | KEY SHARE} [OF table_name [, …]] [NOWAIT | SKIP LOCKED] […]]

— from_item可以是以下之一：
[ONLY] table_name [*] [[AS] alias [(column_alias [, …])]]
[TABLESAMPLE sampling_method (argument [, …]) [REPEATABLE (seed)]]
(select) [[AS] alias [(column_alias [, …])]]
with_query_name [[AS] alias [(column_alias [, …])]]
from_item [NATURAL] join_type from_item [ON join_condition | USING (join_column [, …])]

— 基本查询示例
SELECT * FROM fgedu_customers;

— 输出结果
id | customer_name | phone | email | create_time
—-+—————+————–+——————–+———————
1 | 张三 | 13800138000 | zhangsan@test.com | 2026-04-07 10:00:00
2 | 李四 | 13900139000 | lisi@test.com | 2026-04-07 11:00:00
(2 rows)

— 条件查询
SELECT customer_name, phone
FROM fgedu_customers
WHERE create_time >= ‘2026-04-01’
ORDER BY customer_name;

— 输出结果
customer_name | phone
—————+————–
张三 | 13800138000
李四 | 13900139000
(2 rows)

— DISTINCT去重
SELECT DISTINCT status FROM fgedu_orders;

— 输出结果
status
———-
pending
paid
shipped
(3 rows)

— DISTINCT ON去重
SELECT DISTINCT ON (customer_id)
customer_id, order_no, amount
FROM fgedu_orders
ORDER BY customer_id, amount DESC;

— 输出结果
customer_id | order_no | amount
————-+———-+———-
1 | ORD005 | 5000.00
2 | ORD008 | 8000.00
3 | ORD010 | 10000.00
(3 rows)

— 聚合查询
SELECT
status,
COUNT(*) AS order_count,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount,
MAX(amount) AS max_amount,
MIN(amount) AS min_amount
FROM fgedu_orders
GROUP BY status
HAVING COUNT(*) > 10
ORDER BY total_amount DESC;

— 输出结果
status | order_count | total_amount | avg_amount | max_amount | min_amount
———-+————-+————–+——————+————+————
pending | 25 | 125000.00 | 5000.00000000000 | 10000.00 | 1000.00
paid | 20 | 100000.00 | 5000.00000000000 | 8000.00 | 2000.00
shipped | 15 | 75000.00 | 5000.00000000000 | 6000.00 | 3000.00
(3 rows)

— JOIN连接查询
SELECT
o.order_no,
c.customer_name,
o.amount,
o.status
FROM fgedu_orders o
INNER JOIN fgedu_customers c ON o.customer_id = c.id
WHERE o.amount > 5000
ORDER BY o.amount DESC;

— 输出结果
order_no | customer_name | amount | status
———-+—————+———-+———
ORD010 | 张三 | 10000.00 | pending
ORD008 | 李四 | 8000.00 | paid
ORD005 | 王五 | 6000.00 | shipped
(3 rows)

— LEFT JOIN左连接
SELECT
c.customer_name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.amount), 0) AS total_amount
FROM fgedu_customers c
LEFT JOIN fgedu_orders o ON c.id = o.customer_id
GROUP BY c.id, c.customer_name
ORDER BY total_amount DESC;

— 输出结果
customer_name | order_count | total_amount
—————+————-+————–
张三 | 5 | 25000.00
李四 | 3 | 15000.00
王五 | 0 | 0.00
(3 rows)

— 子查询
SELECT customer_name
FROM fgedu_customers
WHERE id IN (
SELECT customer_id
FROM fgedu_orders
WHERE amount > 5000
);

— 输出结果
customer_name
—————
张三
李四
(2 rows)

— EXISTS子查询
SELECT c.customer_name
FROM fgedu_customers c
WHERE EXISTS (
SELECT 1 FROM fgedu_orders o
WHERE o.customer_id = c.id
AND o.status = ‘pending’
);

— 输出结果
customer_name
—————
张三
王五
(3 rows)

— CTE公共表表达式
WITH monthly_sales AS (
SELECT
DATE_TRUNC(‘month’, create_time) AS month,
SUM(amount) AS total_sales
FROM fgedu_orders
WHERE create_time >= ‘2026-01-01’
GROUP BY DATE_TRUNC(‘month’, create_time)
)
SELECT
TO_CHAR(month, ‘YYYY-MM’) AS month,
total_sales,
LAG(total_sales) OVER (ORDER BY month) AS prev_month,
total_sales – LAG(total_sales) OVER (ORDER BY month) AS growth
FROM monthly_sales
ORDER BY month;

— 输出结果
month | total_sales | prev_month | growth
———+————-+————+———-
2026-01 | 100000.00 | |
2026-02 | 120000.00 | 100000.00 | 20000.00
2026-03 | 150000.00 | 120000.00 | 30000.00
2026-04 | 80000.00 | 150000.00 | -70000.00
(4 rows)

— 窗口函数
SELECT
order_no,
customer_id,
amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank,
SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
AVG(amount) OVER () AS global_avg
FROM fgedu_orders
ORDER BY customer_id, amount DESC;

— 输出结果
order_no | customer_id | amount | rank | customer_total | global_avg
———-+————-+———+——+—————-+——————–
ORD005 | 1 | 5000.00 | 1 | 15000.00 | 4500.00000000000000
ORD003 | 1 | 4000.00 | 2 | 15000.00 | 4500.00000000000000
ORD001 | 1 | 3000.00 | 3 | 15000.00 | 4500.00000000000000
ORD008 | 2 | 8000.00 | 1 | 12000.00 | 4500.00000000000000
ORD006 | 2 | 4000.00 | 2 | 12000.00 | 4500.00000000000000
(5 rows)

— 分页查询
SELECT * FROM fgedu_orders
ORDER BY id
LIMIT 10 OFFSET 0;

— 输出结果
id | order_no | customer_id | amount | status
—-+———-+————-+———+———
1 | ORD001 | 1 | 1000.00 | pending
2 | ORD002 | 1 | 2000.00 | paid
3 | ORD003 | 1 | 3000.00 | shipped
(10 rows)

— FETCH分页
SELECT * FROM fgedu_orders
ORDER BY id
FETCH FIRST 10 ROWS ONLY;

— 输出结果
id | order_no | customer_id | amount | status
—-+———-+————-+———+———
1 | ORD001 | 1 | 1000.00 | pending
2 | ORD002 | 1 | 2000.00 | paid
(10 rows)

— 授予表权限
GRANT { {SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER}
[, …] | ALL [PRIVILEGES] }
ON { [TABLE] table_name [, …]
| ALL TABLES IN SCHEMA schema_name [, …] }
TO { [GROUP] role_name | PUBLIC } [, …] [WITH GRANT OPTION]

— 授予列权限
GRANT { {SELECT | INSERT | UPDATE | REFERENCES} (column_name [, …])
[, …] | ALL [PRIVILEGES] (column_name [, …]) }
ON [TABLE] table_name [, …]
TO { [GROUP] role_name | PUBLIC } [, …] [WITH GRANT OPTION]

— 授予序列权限
GRANT { {USAGE | SELECT | UPDATE}
[, …] | ALL [PRIVILEGES] }
ON { SEQUENCE sequence_name [, …]
| ALL SEQUENCES IN SCHEMA schema_name [, …] }
TO { [GROUP] role_name | PUBLIC } [, …] [WITH GRANT OPTION]

— 授予数据库权限
GRANT { {CREATE | CONNECT | TEMPORARY | TEMP} [, …] | ALL [PRIVILEGES] }
ON DATABASE database_name [, …]
TO { [GROUP] role_name | PUBLIC } [, …] [WITH GRANT OPTION]

— 授予函数权限
GRANT { EXECUTE | ALL [PRIVILEGES] }
ON { FUNCTION function_name ( [ [argmode] [argname] argtype [, …]] ) [, …]
| ALL FUNCTIONS IN SCHEMA schema_name [, …] }
TO { [GROUP] role_name | PUBLIC } [, …] [WITH GRANT OPTION]

— 授予角色权限
GRANT role_name [, …] TO role_name [, …] [WITH ADMIN OPTION]

— 创建测试用户
CREATE USER fgedu_readonly WITH PASSWORD ‘readonly_2026’;
CREATE USER fgedu_readwrite WITH PASSWORD ‘readwrite_2026’;
CREATE USER fgedu_admin WITH PASSWORD ‘admin_2026’;

— 输出结果
CREATE ROLE
CREATE ROLE
CREATE ROLE

— 授予只读权限
GRANT CONNECT ON DATABASE fgedudb TO fgedu_readonly;
GRANT USAGE ON SCHEMA public TO fgedu_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fgedu_readonly;

— 输出结果
GRANT
GRANT
GRANT

— 授予读写权限
GRANT CONNECT ON DATABASE fgedudb TO fgedu_readwrite;
GRANT USAGE ON SCHEMA public TO fgedu_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO fgedu_readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO fgedu_readwrite;

— 输出结果
GRANT
GRANT
GRANT
GRANT

— 授予管理权限
GRANT ALL PRIVILEGES ON DATABASE fgedudb TO fgedu_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO fgedu_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO fgedu_admin;

— 输出结果
GRANT
GRANT
GRANT

— 授予列级权限
GRANT SELECT (customer_name, phone) ON fgedu_customers TO fgedu_readonly;

— 输出结果
GRANT

— 授予函数执行权限
GRANT EXECUTE ON FUNCTION fgedu_validate_email(text) TO PUBLIC;

— 输出结果
GRANT

— 授予角色
CREATE ROLE fgedu_readonly_role;
GRANT fgedu_readonly TO fgedu_readonly_role;

— 输出结果
CREATE ROLE
GRANT ROLE

— 设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO fgedu_readonly;

— 输出结果
ALTER DEFAULT PRIVILEGES

— 撤销表权限
REVOKE [GRANT OPTION FOR]
{ {SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER}
[, …] | ALL [PRIVILEGES] }
ON { [TABLE] table_name [, …]
| ALL TABLES IN SCHEMA schema_name [, …] }
FROM { [GROUP] role_name | PUBLIC } [, …]
[CASCADE | RESTRICT]

— 撤销角色权限
REVOKE [ADMIN OPTION FOR]
role_name [, …] FROM role_name [, …]
[CASCADE | RESTRICT]

— 撤销权限示例
REVOKE SELECT ON fgedu_customers FROM fgedu_readonly;

— 输出结果
REVOKE

— 撤销所有权限
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM fgedu_readonly;

— 输出结果
REVOKE

— 撤销角色
REVOKE fgedu_readonly FROM fgedu_readonly_role;

— 输出结果
REVOKE ROLE

— 撤销默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT ON TABLES FROM fgedu_readonly;

— 输出结果
ALTER DEFAULT PRIVILEGES

— 查看权限
SELECT
grantee,
table_schema,
table_name,
privilege_type
FROM information_schema.table_privileges
WHERE grantee = ‘fgedu_readonly’
ORDER BY table_name;

— 输出结果
grantee | table_schema | table_name | privilege_type
——————+————–+—————-+—————-
fgedu_readonly | public | fgedu_orders | SELECT
fgedu_readonly | public | fgedu_products | SELECT
(2 rows)

— 查看角色权限
SELECT
r.rolname,
r.rolsuper,
r.rolcreaterole,
r.rolcreatedb,
r.rolcanlogin
FROM pg_roles r
WHERE r.rolname LIKE ‘fgedu%’;

— 输出结果
rolname | rolsuper | rolcreaterole | rolcreatedb | rolcanlogin
——————+———-+—————+————-+————-
fgedu_readonly | f | f | f | t
fgedu_readwrite | f | f | f | t
fgedu_admin | f | f | f | t
(3 rows)

— 多表连接查询
SELECT
c.customer_name,
o.order_no,
p.product_name,
oi.quantity,
oi.price,
oi.quantity * oi.price AS subtotal
FROM fgedu_customers c
INNER JOIN fgedu_orders o ON c.id = o.customer_id
INNER JOIN fgedu_order_items oi ON o.id = oi.order_id
INNER JOIN fgedu_products p ON oi.product_id = p.id
WHERE o.create_time >= ‘2026-04-01’
ORDER BY o.order_no, oi.id;

— 输出结果
customer_name | order_no | product_name | quantity | price | subtotal
—————+———-+————–+———-+———+———-
张三 | ORD001 | iPhone 15 | 2 | 7999.00 | 15998.00
张三 | ORD001 | iPad Air | 1 | 4999.00 | 4999.00
李四 | ORD002 | MacBook Pro | 1 |14999.00 | 14999.00
(3 rows)

— 分组统计查询
SELECT
TO_CHAR(o.create_time, ‘YYYY-MM’) AS month,
c.customer_name,
COUNT(DISTINCT o.id) AS order_count,
SUM(oi.quantity * oi.price) AS total_amount
FROM fgedu_orders o
JOIN fgedu_customers c ON o.customer_id = c.id
JOIN fgedu_order_items oi ON o.id = oi.order_id
GROUP BY TO_CHAR(o.create_time, ‘YYYY-MM’), c.customer_name
HAVING SUM(oi.quantity * oi.price) > 10000
ORDER BY month, total_amount DESC;

— 输出结果
month | customer_name | order_count | total_amount
———+—————+————-+————–
2026-04 | 张三 | 5 | 50000.00
2026-04 | 李四 | 3 | 30000.00
(2 rows)

— 排名查询
SELECT
customer_name,
total_amount,
RANK() OVER (ORDER BY total_amount DESC) AS rank,
DENSE_RANK() OVER (ORDER BY total_amount DESC) AS dense_rank,
ROW_NUMBER() OVER (ORDER BY total_amount DESC) AS row_num
FROM (
SELECT
c.customer_name,
SUM(o.amount) AS total_amount
FROM fgedu_customers c
LEFT JOIN fgedu_orders o ON c.id = o.customer_id
GROUP BY c.id, c.customer_name
) t;

— 输出结果
customer_name | total_amount | rank | dense_rank | row_num
—————+————–+——+————+———
张三 | 50000.00 | 1 | 1 | 1
李四 | 30000.00 | 2 | 2 | 2
王五 | 20000.00 | 3 | 3 | 3
(3 rows)

— 递归查询（组织架构）
WITH RECURSIVE org_tree AS (
SELECT
id,
employee_name,
manager_id,
1 AS level,
employee_name::TEXT AS path
FROM fgedu_employees
WHERE manager_id IS NULL

UNION ALL

SELECT
e.id,
e.employee_name,
e.manager_id,
t.level + 1,
t.path || ‘ > ‘ || e.employee_name
FROM fgedu_employees e
INNER JOIN org_tree t ON e.manager_id = t.id
)
SELECT
REPEAT(‘ ‘, level – 1) || employee_name AS org_chart,
level,
path
FROM org_tree
ORDER BY path;

— 输出结果
org_chart | level | path
—————–+——-+—————————
CEO | 1 | CEO
CTO | 2 | CEO > CTO
开发经理 | 3 | CEO > CTO > 开发经理
测试经理 | 3 | CEO > CTO > 测试经理
CFO | 2 | CEO > CFO
财务主管 | 3 | CEO > CFO > 财务主管
(6 rows)

— 透视表查询
SELECT
customer_name,
SUM(CASE WHEN status = ‘pending’ THEN amount ELSE 0 END) AS pending_amount,
SUM(CASE WHEN status = ‘paid’ THEN amount ELSE 0 END) AS paid_amount,
SUM(CASE WHEN status = ‘shipped’ THEN amount ELSE 0 END) AS shipped_amount,
SUM(amount) AS total_amount
FROM (
SELECT c.customer_name, o.amount, o.status
FROM fgedu_customers c
JOIN fgedu_orders o ON c.id = o.customer_id
) t
GROUP BY customer_name
ORDER BY total_amount DESC;

— 输出结果
customer_name | pending_amount | paid_amount | shipped_amount | total_amount
—————+—————-+————-+—————-+————–
张三 | 10000.00 | 20000.00 | 20000.00 | 50000.00
李四 | 5000.00 | 15000.00 | 10000.00 | 30000.00
(2 rows)

— 创建角色层次结构
CREATE ROLE fgedu_base_role NOLOGIN;
CREATE ROLE fgedu_readonly_role NOLOGIN;
CREATE ROLE fgedu_readwrite_role NOLOGIN;
CREATE ROLE fgedu_admin_role NOLOGIN;

— 输出结果
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE

— 配置基础权限
GRANT CONNECT ON DATABASE fgedudb TO fgedu_base_role;
GRANT USAGE ON SCHEMA public TO fgedu_base_role;

— 输出结果
GRANT
GRANT

— 配置只读角色
GRANT fgedu_base_role TO fgedu_readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fgedu_readonly_role;

— 输出结果
GRANT ROLE
GRANT

— 配置读写角色
GRANT fgedu_readonly_role TO fgedu_readwrite_role;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO fgedu_readwrite_role;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO fgedu_readwrite_role;

— 输出结果
GRANT ROLE
GRANT
GRANT

— 配置管理角色
GRANT fgedu_readwrite_role TO fgedu_admin_role;
GRANT CREATE ON SCHEMA public TO fgedu_admin_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO fgedu_admin_role;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO fgedu_admin_role;

— 输出结果
GRANT ROLE
GRANT
GRANT
GRANT

— 创建用户并分配角色
CREATE USER fgedu_dev WITH PASSWORD ‘dev_2026’;
CREATE USER fgedu_tester WITH PASSWORD ‘tester_2026’;
CREATE USER fgedu_dba WITH PASSWORD ‘dba_2026’;

GRANT fgedu_readwrite_role TO fgedu_dev;
GRANT fgedu_readonly_role TO fgedu_tester;
GRANT fgedu_admin_role TO fgedu_dba;

— 输出结果
CREATE ROLE
CREATE ROLE
CREATE ROLE
GRANT ROLE
GRANT ROLE
GRANT ROLE

— 设置行级安全策略
ALTER TABLE fgedu_orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY fgedu_orders_policy ON fgedu_orders
USING (customer_id IN (
SELECT id FROM fgedu_customers
WHERE created_by = CURRENT_USER
));

— 输出结果
ALTER TABLE
CREATE POLICY

— 审计权限变更
CREATE TABLE fgedu_permission_audit (
id SERIAL PRIMARY KEY,
event_time TIMESTAMP DEFAULT NOW(),
event_type VARCHAR(50),
object_type VARCHAR(50),
object_name VARCHAR(200),
grantee VARCHAR(100),
privileges TEXT,
granted_by VARCHAR(100) DEFAULT CURRENT_USER
);

— 输出结果
CREATE TABLE

— 创建权限审计触发器函数
CREATE OR REPLACE FUNCTION fgedu_audit_grant()
RETURNS EVENT_TRIGGER
AS $$
DECLARE
obj RECORD;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
INSERT INTO fgedu_permission_audit(
event_type, object_type, object_name
)
VALUES (
tg_tag,
obj.object_type,
obj.object_identity
);
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建事件触发器
CREATE EVENT TRIGGER trg_audit_grant
ON ddl_command_end
WHEN tg_tag IN (‘GRANT’, ‘REVOKE’)
EXECUTE FUNCTION fgedu_audit_grant();

— 输出结果
CREATE EVENT TRIGGER

— 创建部门角色
CREATE ROLE fgedu_dept_sales NOLOGIN;
CREATE ROLE fgedu_dept_finance NOLOGIN;
CREATE ROLE fgedu_dept_hr NOLOGIN;

— 输出结果
CREATE ROLE
CREATE ROLE
CREATE ROLE

— 创建业务模式
CREATE SCHEMA sales;
CREATE SCHEMA finance;
CREATE SCHEMA hr;

— 输出结果
CREATE SCHEMA
CREATE SCHEMA
CREATE SCHEMA

— 授予部门权限
GRANT ALL PRIVILEGES ON SCHEMA sales TO fgedu_dept_sales;
GRANT ALL PRIVILEGES ON SCHEMA finance TO fgedu_dept_finance;
GRANT ALL PRIVILEGES ON SCHEMA hr TO fgedu_dept_hr;

— 输出结果
GRANT
GRANT
GRANT

— 创建部门用户
CREATE USER fgedu_sales_user WITH PASSWORD ‘sales_2026’;
CREATE USER fgedu_finance_user WITH PASSWORD ‘finance_2026’;
CREATE USER fgedu_hr_user WITH PASSWORD ‘hr_2026’;

GRANT fgedu_dept_sales TO fgedu_sales_user;
GRANT fgedu_dept_finance TO fgedu_finance_user;
GRANT fgedu_dept_hr TO fgedu_hr_user;

— 输出结果
CREATE ROLE
CREATE ROLE
CREATE ROLE
GRANT ROLE
GRANT ROLE
GRANT ROLE

— 设置用户默认角色
ALTER USER fgedu_sales_user SET ROLE fgedu_dept_sales;
ALTER USER fgedu_finance_user SET ROLE fgedu_dept_finance;
ALTER USER fgedu_hr_user SET ROLE fgedu_dept_hr;

— 输出结果
ALTER ROLE
ALTER ROLE
ALTER ROLE

— 查看角色成员
SELECT
r.rolname AS role,
m.rolname AS member,
a.rolname AS admin
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid
LEFT JOIN pg_roles a ON am.grantor = a.oid
WHERE r.rolname LIKE ‘fgedu_%’
ORDER BY r.rolname, m.rolname;

— 输出结果
role | member | admin
———————+——————-+——-
fgedu_dept_finance | fgedu_finance_user|
fgedu_dept_hr | fgedu_hr_user |
fgedu_dept_sales | fgedu_sales_user |
(3 rows)

— 创建角色管理视图
CREATE VIEW fgedu_role_hierarchy AS
WITH RECURSIVE role_tree AS (
SELECT
oid,
rolname,
rolname::TEXT AS path,
1 AS level
FROM pg_roles
WHERE oid NOT IN (SELECT member FROM pg_auth_members)

UNION ALL

SELECT
r.oid,
r.rolname,
t.path || ‘ > ‘ || r.rolname,
t.level + 1
FROM pg_roles r
INNER JOIN pg_auth_members am ON r.oid = am.member
INNER JOIN role_tree t ON am.roleid = t.oid
)
SELECT * FROM role_tree
WHERE rolname LIKE ‘fgedu%’
ORDER BY path;

— 输出结果
CREATE VIEW

SELECT * FROM fgedu_role_hierarchy;

— 输出结果
oid | rolname | path | level
——-+———————-+——————————–+——-
16384 | fgedu_base_role | fgedu_base_role | 1
16385 | fgedu_readonly_role | fgedu_base_role > fgedu_readonly_role | 2
16386 | fgedu_readwrite_role | fgedu_base_role > fgedu_readonly_role > fgedu_readwrite_role | 3
(3 rows)