PostgreSQL 随笔 临时表 & 视图 & with

0.博客感觉也要跟代码一起逐版迭代了…

PgSQL12 中文文档 CREATE TABLE AS
PgSQL12 中文文档 视图

1. 临时表

存储的是一个临时的表，该表的结果集不会更新

1.1 demo

-- 这个例子，我承认有些生涩...
create temp table if not exists alarmItemTempTable as (
	select p.code as type,array_agg(c.code) as arr from sys_category as c inner join
	(select id,code from sys_category where  pid = (select id from sys_category where code = ''alarm_warning_type'')) as p
	on c.pid = p.id
	group by p.code
)	

-- 调用临时表
select type,arr from alarmItemTempTable

1.2 特点

默认在会话后，被自动清理，可以使用on commit指令指定事务结束后清理。当然，也可以手动清理。

2. 视图

查询SQL的一个引用，用户因此而不必每次输入相同的SQL

2.1 demo

-- 创建一个视图
CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;
-- 调用该视图查询
SELECT * FROM myview;

2.2 特点

因此存储的是SQL,因此返回的数据集是实时更新的
存储的这条SQL支持后续的修改，甚至可以在视图的基础上创建视图

2.3 物化视图

物化视图不能实时的更新结果集，需要手动刷新。

适合非实时数据的查询

2.4 内置视图

从pgsql的两个模式(Schema)划分，内置视图可分为 诸多数据库语言常见的information_schema模式、pgsql特有的pg_catalog模式

2.4.1 information_schema视图

允许用户查询用户相关的表/视图/函数的各种属性

该视图默认是隐藏的

select *  from information_schema.tables
select *  from information_schema.columns

2.4.2 pg_catalog 模式

pgsql系统视图，也有同上的能力

select * from pg_tables 
pselect * from pg_catalog.pg_namespace

3. with

with 是sql原生支持的，不同前两者(应用于存储过程)
使用with 一样可以实现临时表的效果