重庆分公司,新征程启航

为企业提供网站建设、域名注册、服务器等服务

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,...)

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中扩展安装时选择如下图项目,

这样在函数的右键菜单中就有调试选项了,

当然,最常用的调试方式就是自己直接调用函数来查看执行结果是否正确。

如何扩大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

-------

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

如何加密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

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


当前文章:postgresql视频存储的简单介绍
网站链接:http://cqcxhl.com/article/dsdgede.html

其他资讯

在线咨询
服务热线
服务热线:028-86922220
TOP