postgresql存儲過程 怎么實(shí)現(xiàn)無返回值

PostgreSQL 存儲過程定義格式如下：

成都創(chuàng)新互聯(lián)公司堅持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站設(shè)計、網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時代的平果網(wǎng)站設(shè)計、移動媒體設(shè)計的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

■結(jié)構(gòu) PL/pgSQL是一種塊結(jié)構(gòu)的語言，比較方便的是用pgAdmin III新建Function，填入一些參數(shù)就可以了。

基本上是這樣的：

CREATE OR REPLACE FUNCTION 函數(shù)名(參數(shù)1，[整型 int4, 整型數(shù)組 _int4, ...]) RETURNS 返回值類型 AS $BODY$ DECLARE 變量聲明 BEGIN 函數(shù)體 END; $BODY$ LANGUAGE ‘plpgsql’ VOLATILE;

■變量類型 除了postgresql內(nèi)置的變量類型外，常用的還有 RECORD ，表示一條記錄。

■賦值 賦值和Pascal有點(diǎn)像：“變量 := 表達(dá)式;” 有些奇怪的是連接字符串的是“||”，比如 sql := ‘SELECT * FROM’ || table || ‘WHERE …’;

■判斷 判斷又和VB有些像： IF 條件 THEN … ELSEIF 條件 THEN … ELSE … END IF;

■循環(huán) 循環(huán)有好幾種寫法： WHILE expression LOOP statements END LOOP; 還有常用的一種是：（從1循環(huán)到9可以寫成FOR i IN 1..9 LOOP） FOR name IN [ REVERSE ] expression .. expression LOOP statements END LOOP;

■其他 還有幾個常用的函數(shù)： SELECT INTO record …; 表示將select的結(jié)果賦給record變量（RECORD類型） PERFORM query; 表示執(zhí)行query并丟棄結(jié)果 EXECUTE sql; 表示執(zhí)行sql語句，這條可以動態(tài)執(zhí)行sql語句（特別是由參數(shù)傳入構(gòu)造sql語句的時候特別有用）

--簡單的例子：

例1：無返回值

CREATE OR REPLACE FUNCTION 函數(shù)名稱( 參數(shù)1,參數(shù)2,...)

AS

$BODY$

DECLARE --定義

BEGIN

INSERT INTO "表名" VALUES(參數(shù)1，參數(shù)2，...);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE; -- 最后別忘了這個。

例2：有返回值

CREATE OR REPLACE FUNCTION 函數(shù)名稱(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;

最后再加上如何執(zhí)行這個存儲過程（函數(shù)）

-- 執(zhí)行存儲過程方法1

SELECT * FROM 函數(shù)名稱(參數(shù)1，參數(shù)2，...)

-- 執(zhí)行存儲過程方法2

SELECT 函數(shù)名稱('0參數(shù)1，參數(shù)2，...)

postgresql存儲過程怎么用

PostgreSQL有pldbgapi擴(kuò)展，先安裝此擴(kuò)展。

首先，需要將debug的模組載入到PostgreSQL服務(wù)器中去。做法是: 在pgAdminIII中以管理員登錄，然后選擇菜單“工具-服務(wù)器配置-postgresql.conf”，

在配置窗口中，雙擊項目"shared_preload_libraries"，

在其設(shè)定窗口中，將值設(shè)置成plugin_debugger的路徑(Windows 9.3版本的為$libdir/plugin_debugger.dll, $libdir一般為PostgreSQL安裝目錄下的lib文件夾)。非Windows系統(tǒng)的后綴名會有所不同，可能為plugin_debugger.so。

注意，在做這一步時最好先備份配置文件，以防指定文件找不到而導(dǎo)致服務(wù)器不能啟動。

在設(shè)定完成之后，重新啟動PostgreSQL服務(wù)(以啟動debug插件)。

然后在pgAdminIII中擴(kuò)展安裝時選擇如下圖項目，

這樣在函數(shù)的右鍵菜單中就有調(diào)試選項了，

當(dāng)然，最常用的調(diào)試方式就是自己直接調(diào)用函數(shù)來查看執(zhí)行結(jié)果是否正確。

如何擴(kuò)大postgresql存儲空間

1. 概述

cstore_fdw實(shí)現(xiàn)了 PostgreSQL 數(shù)據(jù)庫的列式存儲。列存儲非常適合用于數(shù)據(jù)分析的場景，數(shù)據(jù)分析的場景下數(shù)據(jù)是批量加載的。

這個擴(kuò)展使用了Optimized Row Columnar (ORC)數(shù)據(jù)存儲格式，ORC改進(jìn)了Facebook的RCFile格式，帶來如下好處:

壓縮:將內(nèi)存和磁盤中數(shù)據(jù)大小削減到2到4倍。可以擴(kuò)展以支持不同壓縮算法。

列投影:只提取和查詢相關(guān)的列數(shù)據(jù)。提升IO敏感查詢的性能。

跳過索引:為行組存儲最大最小統(tǒng)計值，并利用它們跳過無關(guān)的行。

2. 使用

cstore_fdw的安裝和使用都非常簡單，可以參考官方資料。

thub.com/citusdata/cstore_fdw

注)注意cstore_fdw只支持PostgreSQL9.3和9.4 。

下面做幾個簡單的性能對比，看看cstore_fdw究竟能帶來多大的性能提升。

2.1 數(shù)據(jù)加載

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千萬條記錄，數(shù)據(jù)占用存儲大小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千萬條記錄，數(shù)據(jù)占用存儲大小390M，插入耗時7分35秒，分析耗時6秒。

使用cstore列存儲后，數(shù)據(jù)占用存儲大小降到普通表的3分之1。需要說明的是，由于所有TEXT列填充了隨機(jī)數(shù)據(jù)，壓縮率不算高，某些實(shí)際的應(yīng)用場景下壓縮效果會比這更好。

2.2 Text列的like查詢性能對比

2.2.1 普通表

清除文件系統(tǒng)緩存，并重啟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列執(zhí)行l(wèi)ike查詢

[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秒，產(chǎn)生IO讀1.14G,IO寫108K。

不清文件系統(tǒng)緩存，不重啟PostgreSQL，再執(zhí)行一次。消耗時間降到1.6秒，幾乎不產(chǎn)生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表

清除文件系統(tǒng)緩存，并重啟PostgreSQL

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches

對Text列執(zhí)行l(wèi)ike查詢

[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秒，產(chǎn)生IO讀59M,IO寫0K。執(zhí)行時間優(yōu)化的雖然不是太多，但I(xiàn)O大大減少，可見列投影起到了作用。

不清文件系統(tǒng)緩存，不重啟PostgreSQL，再執(zhí)行一次。消耗時間降到1.4秒，幾乎不產(chǎn)生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列執(zhí)行=查詢

2.3.1 普通表

清除文件系統(tǒng)緩存，并重啟PostgreSQL后

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches

對Int列執(zhí)行=查詢

[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秒，產(chǎn)生IO讀1.14G,IO寫84K

不清緩存，再執(zhí)行一次。消耗時間降到1.1秒，幾乎不產(chǎn)生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存儲二進(jìn)制大數(shù)據(jù)文件

title: "Postgresql存儲二進(jìn)制大數(shù)據(jù)文件"

date: 2021-02-02T20:46:31+08:00

draft: true

tags: ['postgres','binary']

author: "dadigang"

author_cn: "大地缸"

personal: " "

如果想把整個文件或圖片存儲在數(shù)據(jù)表的一個字段內(nèi)，該字段可以選擇二進(jìn)制類型，然后將文件按二進(jìn)制存儲起來，文本文件也可以存在text字段內(nèi)。

示例如下：

二進(jìn)制類型bytea的操作（在最大值內(nèi)，有內(nèi)存限制）

1、 創(chuàng)建表

2、 將文件放到coordinator目錄下/mnt/postgresql/coord

通過pg_read_binary_file()函數(shù)，插入一張圖片- 目錄：/mnt/postgresql/coord/1.jpg

3、 也可以調(diào)用pg_read _file()將一個文本文件存儲在一個text字段內(nèi)

注意：函數(shù)pg_read_binary_file()和pg_read_file()中的路徑必須是相對路徑，默認(rèn)路徑是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的存儲過程

寫個觸發(fā)器 插入之前執(zhí)行觸發(fā)器

-- 創(chuàng)建一個測試表

create table test(id int primary key , name varchar(50));

-- 觸發(fā)器 插入前ID如果已經(jīng)存在則替換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;

-- 給表加上觸發(fā)器

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;

結(jié)果：

pumpkin= select * from test;

id | name

----+------

1 | 6

(1 行記錄)

時間：1.474 ms

PostgreSQL Oracle 兼容性之存儲過程

?在oracle中，函數(shù)和存儲過程是經(jīng)常使用到的，并且有所區(qū)別；而postgresql中函數(shù)和存儲過程都是相同定義的。

?1.定義：定義存儲過程的關(guān)鍵字為procedure。

?2.創(chuàng)建存儲過程

例：

（無參數(shù)）

（有參有返）

總結(jié) ：

?1.創(chuàng)建存儲過程的關(guān)鍵字為procedure。

?2.傳參列表中的參數(shù)可以用in，out，in out修飾，參數(shù)類型一定不能寫大小。列表中可以有多個輸入輸出參數(shù)。

?3.存儲過程中定義的參數(shù)列表不需要用declare聲明，聲明參數(shù)類型時需要寫大小的一定要帶上大小。

?4.as可以用is替換。

?5.調(diào)用帶輸出參數(shù)的過程必須要聲明變量來接收輸出參數(shù)值。

?6.執(zhí)行存儲過程有兩種方式，一種是使用execute，另一種是用begin和end包住。

?1.定義：定義函數(shù)的關(guān)鍵字為function。

?2.創(chuàng)建函數(shù)

總結(jié) ：

?1.定義函數(shù)的關(guān)鍵字為function 。

?2.必須有返回值，且聲明返回值類型時不需要加大小。

?3.函數(shù)中定義的參數(shù)列表不需要用declare聲明，聲明參數(shù)類型時需要寫大小的一定要帶上大小。

?4.as可以用is替換。

? 5.執(zhí)行存儲過程有兩種方式，一種是使用select，另一種是用begin和end包住。

不同點(diǎn)：

?1.存儲過程定義關(guān)鍵字用procedure，函數(shù)定義用function。

?2.存儲過程中不能用return返回值，但函數(shù)中可以，而且函數(shù)中必須有return子句。

?3.執(zhí)行方式略有不同，存儲過程的執(zhí)行方式有兩種（1.使用execute2.使用begin和end），函數(shù)除了存儲過程的兩種方式外，還可以當(dāng)做表達(dá)式使用，例如放在select中（select f1() form dual;）。

?postgresql則將函數(shù)和存儲過程合為一體，不再明確區(qū)分存儲過程與函數(shù)。

?1.定義：定義函數(shù)（存儲過程 ）的關(guān)鍵字為function。

?2.創(chuàng)建

例：

?1.必須有有returns（注意是returns不是return）子句，無返回值時returns viod

?2.執(zhí)行時，有返回值用select，無返回值時用perform

?3.必須指定語言LANGUAGE