正文
postgresql视频存储的简单介绍
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
postgresql存储过程 怎么实现无返回值
PostgreSQL 存储过程定义格式如下：
■结构 PL/pgSQL是一种块结构的语言，比较方便的是用pgAdmin III新建Function，填入一些参数就可以了。
基本上是这样的：
CREATE OR REPLACE FUNCTION 函数名(参数1，[整型 int4, 整型数组 _int4, ...]) RETURNS 返回值类型 AS $BODY$ DECLARE 变量声明 BEGIN 函数体 END; $BODY$ LANGUAGE ‘plpgsql’ VOLATILE;
■变量类型 除了postgresql内置的变量类型外，常用的还有 RECORD ，表示一条记录。
■赋值 赋值和Pascal有点像：“变量 := 表达式;” 有些奇怪的是连接字符串的是“||”，比如 sql := ‘SELECT * FROM’ || table || ‘WHERE …’;
■判断 判断又和VB有些像： IF 条件 THEN … ELSEIF 条件 THEN … ELSE … END IF;
■循环 循环有好几种写法： WHILE expression LOOP statements END LOOP; 还有常用的一种是：（从1循环到9可以写成FOR i IN 1..9 LOOP） FOR name IN [ REVERSE ] expression .. expression LOOP statements END LOOP;
■其他 还有几个常用的函数： SELECT INTO record …; 表示将select的结果赋给record变量（RECORD类型） PERFORM query; 表示执行query并丢弃结果 EXECUTE sql; 表示执行sql语句，这条可以动态执行sql语句（特别是由参数传入构造sql语句的时候特别有用）
--简单的例子：
例1：无返回值
CREATE OR REPLACE FUNCTION 函数名称( 参数1,参数2,...)
AS
$BODY$
DECLARE --定义
BEGIN
INSERT INTO "表名" VALUES(参数1，参数2，...);
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE; -- 最后别忘了这个。
例2：有返回值
CREATE OR REPLACE FUNCTION 函数名称(deptcode VARCHAR(20) ,deptname VARCHAR(60) ,pycode VARCHAR(60),isenabled CHAR(1))
RETURNS BOOLEAN --返回值，布尔类型
AS
$body$
DECLARE
deptcode VARCHAR(20);
deptname VARCHAR(60);
pycode VARCHAR(60);
isenabled CHAR(1);
BEGIN
UPDATE "deptDict" SET deptcode=deptcode,deptname=deptname,pycode=pycode,isenabled=isenabled,updatedhisdatetime=CURRENT_TIMESTAMP
WHERE deptcode=deptcode;
RETURN TRUE;
END
$body$
LANGUAGE 'plpgsql' VOLATILE;
最后再加上如何执行这个存储过程（函数）
-- 执行存储过程方法1
SELECT * FROM 函数名称(参数1，参数2，...)
-- 执行存储过程方法2
SELECT 函数名称('0参数1，参数2，...)
相关问答
Q1: postgresql存储过程怎么用
PostgreSQL有pldbgapi扩展，先安装此扩展。
首先，需要将debug的模组载入到PostgreSQL服务器中去。做法是: 在pgAdminIII中以管理员登录，然后选择菜单“工具-服务器配置-postgresql.conf”，
在配置窗口中，双击项目"shared_preload_libraries"，
在其设定窗口中，将值设置成plugin_debugger的路径(Windows 9.3版本的为$libdir/plugin_debugger.dll, $libdir一般为PostgreSQL安装目录下的lib文件夹)。非Windows系统的后缀名会有所不同，可能为plugin_debugger.so。
注意，在做这一步时最好先备份配置文件，以防指定文件找不到而导致服务器不能启动。
在设定完成之后，重新启动PostgreSQL服务(以启动debug插件)。
然后在pgAdminIII中扩展安装时选择如下图项目，
这样在函数的右键菜单中就有调试选项了，
当然，最常用的调试方式就是自己直接调用函数来查看执行结果是否正确。
Q2: 如何扩大postgresql存储空间
1. 概述
cstore_fdw实现了 PostgreSQL 数据库的列式存储。列存储非常适合用于数据分析的场景，数据分析的场景下数据是批量加载的。
这个扩展使用了Optimized Row Columnar (ORC)数据存储格式，ORC改进了Facebook的RCFile格式，带来如下好处:
压缩:将内存和磁盘中数据大小削减到2到4倍。可以扩展以支持不同压缩算法。
列投影:只提取和查询相关的列数据。提升IO敏感查询的性能。
跳过索引:为行组存储最大最小统计值，并利用它们跳过无关的行。
2. 使用
cstore_fdw的安装和使用都非常简单，可以参考官方资料。
thub.com/citusdata/cstore_fdw
注)注意cstore_fdw只支持PostgreSQL9.3和9.4 。
下面做几个简单的性能对比，看看cstore_fdw究竟能带来多大的性能提升。
2.1 数据加载
2.1.1 普通表
CREATE TABLE tb1
(
id int,
c1 TEXT,
c2 TEXT,
c3 TEXT,
c4 TEXT,
c5 TEXT,
c6 TEXT,
c7 TEXT,
c8 TEXT,
c9 TEXT,
c10 TEXT
);
注:要和普通表的全表扫描作对比，所以不建主键和索引。
[postgres@node2 chenhj]$ time psql -p 40382 -At -F, -c "select id,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy tb1 from STDIN with CSV"
COPY 10000000
1.56user 1.00system 6:42.39elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k
776inputs+0outputs (17major+918minor)pagefaults 0swaps
real 6m42.402s
user 0m15.174s
sys 0m14.904s
postgres=# select pg_total_relation_size('tb1'::regclass);
pg_total_relation_size
------------------------
1161093120
(1 row)
postgres=# \timing
Timing is on.
postgres=# analyze tb1;
ANALYZE
Time: 11985.070 ms
插入1千万条记录，数据占用存储大小1.16G，插入耗时6分42秒，分析耗时12秒。
2.1.2 cstore表
$ mkdir -p /home/chenhj/data94/cstore
CREATE EXTENSION cstore_fdw;
CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;
CREATE FOREIGN TABLE cstb1
(
id int,
c1 TEXT,
c2 TEXT,
c3 TEXT,
c4 TEXT,
c5 TEXT,
c6 TEXT,
c7 TEXT,
c8 TEXT,
c9 TEXT,
c10 TEXT
)
SERVER cstore_server
OPTIONS(filename '/home/chenhj/data94/cstore/cstb1.cstore',
compression 'pglz');
[postgres@node2 chenhj]$ time psql -p 40382 -At -F, -c "select id,id::text,id::text,id::text,id::text, id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy cstb1 from STDIN with CSV"
COPY 10000000
1.53user 0.78system 7:35.15elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k
968inputs+0outputs (20major+920minor)pagefaults 0swaps
real 7m35.520s
user 0m14.809s
sys 0m14.170s
[postgres@node2 chenhj]$ ls -l /home/chenhj/data94/cstore/cstb1.cstore
-rw------- 1 postgres postgres 389583021 Jun 23 17:32 /home/chenhj/data94/cstore/cstb1.cstore
postgres=# \timing
Timing is on.
postgres=# analyze cstb1;
ANALYZE
Time: 5946.476 ms
插入1千万条记录，数据占用存储大小390M，插入耗时7分35秒，分析耗时6秒。
使用cstore列存储后，数据占用存储大小降到普通表的3分之1。需要说明的是，由于所有TEXT列填充了随机数据，压缩率不算高，某些实际的应用场景下压缩效果会比这更好。
2.2 Text列的like查询性能对比
2.2.1 普通表
清除文件系统缓存，并重启PostgreSQL
[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart
[root@node2 ~]# free
total used free shared buffers cached
Mem: 2055508 771356 1284152 0 9900 452256
-/+ buffers/cache: 309200 1746308
Swap: 4128760 387624 3741136
[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches
[root@node2 ~]# free
total used free shared buffers cached
Mem: 2055508 326788 1728720 0 228 17636
-/+ buffers/cache: 308924 1746584
Swap: 4128760 381912 3746848
对Text列执行like查询
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.42 0.00 95.40
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.55 330.68 212.08 7351441 4714848
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"
count
--------
100000
(1 row)
real 0m7.051s
user 0m0.001s
sys 0m0.004s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.43 0.00 95.39
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.90 381.53 211.90 8489597 4714956
耗时7.1秒，产生IO读1.14G,IO写108K。
不清文件系统缓存，不重启PostgreSQL，再执行一次。消耗时间降到1.6秒，几乎不产生IO。
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.43 0.00 95.39
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.81 332.20 213.06 7350301 4714364
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"
count
--------
100000
(1 row)
real 0m1.601s
user 0m0.002s
sys 0m0.001s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.43 0.00 95.38
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.80 332.12 213.01 7350337 4714364
2.2.2 cstore表
清除文件系统缓存，并重启PostgreSQL
[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart
[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches
对Text列执行like查询
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.38 0.00 95.45
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.12 376.42 209.04 8492017 4716048
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"
count
--------
100000
(1 row)
real 0m2.786s
user 0m0.002s
sys 0m0.003s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.38 0.00 95.44
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.12 378.75 208.89 8550761 4716048
耗时2.8秒，产生IO读59M,IO写0K。执行时间优化的虽然不是太多，但IO大大减少，可见列投影起到了作用。
不清文件系统缓存，不重启PostgreSQL，再执行一次。消耗时间降到1.4秒，几乎不产生IO。
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.36 0.00 95.47
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.75 376.33 207.58 8550809 4716524
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"
count
--------
100000
(1 row)
real 0m1.424s
user 0m0.002s
sys 0m0.001s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.80 0.00 0.38 3.36 0.00 95.47
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.70 375.96 207.38 8550809 4716588
2.3 对Int列执行=查询
2.3.1 普通表
清除文件系统缓存，并重启PostgreSQL后
[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart
[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches
对Int列执行=查询
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.79 0.00 0.37 3.33 0.00 95.50
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.25 373.21 205.67 8560897 4717624
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"
count
-------
1
(1 row)
real 0m6.844s
user 0m0.002s
sys 0m0.006s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.79 0.00 0.37 3.34 0.00 95.49
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.60 422.57 205.54 9699161 4717708
耗时6.8秒，产生IO读1.14G,IO写84K
不清缓存，再执行一次。消耗时间降到1.1秒，几乎不产生IO。
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.79 0.00 0.37 3.33 0.00 95.50
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.44 421.37 204.97 9699177 4718032
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"
count
-------
Q3: Postgresql存储二进制大数据文件
title: "Postgresql存储二进制大数据文件"
date: 2021-02-02T20:46:31+08:00
draft: true
tags: ['postgres','binary']
author: "dadigang"
author_cn: "大地缸"
personal: " "
如果想把整个文件或图片存储在数据表的一个字段内，该字段可以选择二进制类型，然后将文件按二进制存储起来，文本文件也可以存在text字段内。
示例如下：
二进制类型bytea的操作（在最大值内，有内存限制）
1、 创建表
2、 将文件放到coordinator目录下/mnt/postgresql/coord
通过pg_read_binary_file()函数，插入一张图片- 目录：/mnt/postgresql/coord/1.jpg
3、 也可以调用pg_read _file()将一个文本文件存储在一个text字段内
注意：函数pg_read_binary_file()和pg_read_file()中的路径必须是相对路径，默认路径是coordinator目录下，并且必须在coordinator目录下或者coordinator目录的子目录下。
Name
Return Type
Description
pg_read_file(filename text [, offset bigint, length bigint])
text
Return the contents of a text file
pg_read_binary_file(filename text [, offset bigint, length bigint])
bytea
Return the contents of a file
Q4: 如何加密postgresql的存储过程
写个触发器 插入之前执行触发器
-- 创建一个测试表
create table test(id int primary key , name varchar(50));
-- 触发器 插入前ID如果已经存在则替换name的值
CREATE OR REPLACE function _replace() RETURNS TRIGGER AS $INSERT$
declare
_has int ;
BEGIN
select id from test where id = NEW.id into _has;
raise notice 'ddd:%' , _has;
if _has 0 then
update test set name = NEW.name where id = NEW.id;
RETURN null;
end if;
return NEW;
END;
$INSERT$
LANGUAGE PLPGSQL;
-- 给表加上触发器
CREATE TRIGGER tbefore BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE _replace();
-- 插入两个值
insert into test(id , name) values(1,'1');
insert into test(id , name) values(1,'6');
select * from test;
结果：
pumpkin= select * from test;
id | name
----+------
1 | 6
(1 行记录)
时间：1.474 ms
Q5: PostgreSQL Oracle 兼容性之存储过程
在oracle中，函数和存储过程是经常使用到的，并且有所区别；而postgresql中函数和存储过程都是相同定义的。
1.定义：定义存储过程的关键字为procedure。
2.创建存储过程
例：
（无参数）
（有参有返）
总结 ：
1.创建存储过程的关键字为procedure。
2.传参列表中的参数可以用in，out，in out修饰，参数类型一定不能写大小。列表中可以有多个输入输出参数。
3.存储过程中定义的参数列表不需要用declare声明，声明参数类型时需要写大小的一定要带上大小。
4.as可以用is替换。
5.调用带输出参数的过程必须要声明变量来接收输出参数值。
6.执行存储过程有两种方式，一种是使用execute，另一种是用begin和end包住。
1.定义：定义函数的关键字为function。
2.创建函数
总结 ：
1.定义函数的关键字为function 。
2.必须有返回值，且声明返回值类型时不需要加大小。
3.函数中定义的参数列表不需要用declare声明，声明参数类型时需要写大小的一定要带上大小。
4.as可以用is替换。
5.执行存储过程有两种方式，一种是使用select，另一种是用begin和end包住。
不同点：
1.存储过程定义关键字用procedure，函数定义用function。
2.存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。
3.执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）。
postgresql则将函数和存储过程合为一体，不再明确区分存储过程与函数。
1.定义：定义函数（存储过程 ）的关键字为function。
2.创建
例：
1.必须有有returns（注意是returns不是return）子句，无返回值时returns viod
2.执行时，有返回值用select，无返回值时用perform
3.必须指定语言LANGUAGE
