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存儲過程怎麼用
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中擴展安裝時選擇如下圖項目，
這樣在函數的右鍵菜單中就有調試選項了，
當然，最常用的調試方式就是自己直接調用函數來查看執行結果是否正確。
㈢ Postgres-存儲過程 return 詳解
https://www.cnblogs.com/Thenext/p/13531947.html
如果返回一個 數字或者字元 比較簡單，那麼多行多列怎麼辦呢，分為以下幾種情況【東西很多，這里只做簡單列舉】
返回多行單列
又分為幾種方式
1. return next，用在 for 循環中
( in_idinteger)RETURNSSETOFvarcharas $$DECLARE v_name varchar;BEGINforv_namein( (selectnamefromtest_result1whereid=in_id)union(selectnamefromtest_result2whereid= in_id) ) loop
RETURNNEXT v_name;
end loop;
return;END;
$$
LANGUAGE PLPGSQL;
注意
1. 循環外還有個 return
2. 需要實現聲明 v_name
2. return query，無需 for 循環
( in_idinteger)RETURNSSETOFvarcharas $$DECLARE v_rec RECORD;BEGINreturnquery ( (selectnamefromtest_result1whereid=in_id)union(selectnamefromtest_result2whereid= in_id) );
return;END;$$LANGUAGE PLPGSQL;
注意：如果 返回類型為 setof，最好用如下方法
RETURNQUERYEXECUTESQL
不要這么用
executesqlinto out;returnout;
返回多行多列
也有多種方式
1. 使用 return next 和 setof record ，需要 for 循環
( in_idinteger)RETURNSSETOF RECORDas $$DECLARE v_rec RECORD; BEGINforv_recin( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) )loop
RETURNNEXT v_rec;
end loop;
return;END;
$$
LANGUAGE PLPGSQL;
注意
1. 讀取表的整行數據時才能用 record
2. 如果讀取的數據不是整行，需要自定義 復合數據類型，否則會報如下錯誤
ERROR: acolumndefinition listisrequiredforfunctions returning "record"
定義復合類型 ，示例如下
createtype myout2as (
road_num int,
freq bigint);createorreplacefunctiontest(cartext, time1text, time2text)returnssetof myout2as $$declare
array1 text[];
array2 text[];
len1 integer;
len2 integer;
x integer;
y integer;
road_str text;
car_str text;
sql text;
i myout2;
begin-- vin 號拼接selectregexp_split_to_array(car,',')into array2;
selectarray_length(array2,1)into len2;
car_str :='';
y :=1;
whiley<= len2 loop
car_str :=car_str||quote_literal(array2[y])||',';
y :=y+1;
end loop;
-- sql 拼接sql :='select road_number, sum(frequency) from heat_map where date_key >= '''|| time1
||'-01'' and date_key <='''|| time2
||'-20'' and vin in ('||rtrim(car_str,',')
||')group by road_number;';
--execute sql into out;foriinexecute sql loop
returnnext i;
end loop;
return;end$$ language plpgsql;
在執行時可能會報如下錯誤
ERROR:set-valuedfunctioncalledincontext that cannot accept aset
解決方法
select funcname(arg);--改為select*fromfuncname(arg);
2. return query，無需 for 循環
( in_idinteger)RETURNSSETOF RECORDas $$DECLARE v_rec RECORD;BEGINreturnquery ( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) );
return;END;
$$
LANGUAGE PLPGSQL;
3. 使用 out 輸出參數
( in_idinteger,out o_idinteger,out o_namevarchar)
RETURNSSETOF RECORDas $$DECLARE v_rec RECORD;BEGINforv_recin( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) )loop
o_id := v_rec.id;
o_name := v_rec.name;
RETURNNEXT ;
end loop;
return;END;
$$
LANGUAGE PLPGSQL;
總結 - return next && return query
我們可以看到上面無論是單列多行還是多列多行，都用到了 return next 和 return query 方法
在 plpgsql 中，如果存儲過程返回 setof sometype，則返回值必須在 return next 或者 return query 中聲明，然後有一個不帶參數的 retrun 命令，告訴函數執行完畢；【setof 就意味著 多行】
用法如下
RETURNNEXT expression;RETURN QUERY query;RETURNQUERYEXECUTEcommand-string[ USING expression [, ... ]];
return next 可以用於標量和復合類型數據；
return query 命令將查詢到的一條結果追加到函數的結果集中；
二者在單一集合返回函數中自由混合，在這種情況下，結果將被級聯。【有待研究】
return query execute 是 return query 的變形，它指定 sql 將被動態執行；
returnqueryselectroad_number,sum(frequency)fromheat_mapgroupbyroad_number;--這樣可以sql :='select road_number, sum(frequency) from heat_map group by road_number';returnquery sql;--這樣不行
參考資料：
https://blog.csdn.net/victor_ww/article/details/44415895postgresql自定義類型並返回數組
https://blog.csdn.net/weixin_42767321/article/details/92992935PG return next & return query
https://blog.csdn.net/luojin/article/details/45487373PostgreSQL function返回多列多行
https://www.cnblogs.com/xiongsd/archive/2013/06/05/3118704.html返回結果集多列和單列的例子
https://www.cnblogs.com/lottu/p/7404722.html PostgreSQL存儲過程(1)-基於SQL的存儲過程
https://blog.csdn.net/pg_hgdb/article/details/79692749Postgresql動態SQL
https://stackoverflow.com/questions/40864464/postgresql-pgadmin-error-return-cannot-have-a-parameter-in-function-returning-s/40864898 postgresql, pgadmin error RETURN cannot have a parameter in function returning set
https://blog.csdn.net/qq_42535651/article/details/92089510postgresql存儲過程輸出參數
https://www.cnblogs.com/winkey4986/p/6437811.html
https://www.cnblogs.com/lottu/p/7405829.html PostgreSQL存儲過程(3)-流程式控制制語句
㈣ 如何通過Mybatis調用PostgreSQL存儲過程實現數組入參傳遞功能
1.調用沒有OUT參數的存孝或儲過程：
創建存儲過程：
create or replace function get_code(a1 varchar(32)) returns varchar(32) as $$
declare the_result varchar(32);
begin
the_result := name from t_project where id = a1;
return the_result;
end;
$$
language plpgsql;
sqlMap配置文件：
<select id="f1" resultType="String" parameterType="map" statementType="CALLABLE" useCache="false">
<![CDATA[
select get_code(
#{a1,mode=IN,jdbcType=VARCHAR}
)
]]>
</select>
註：不使用OUT參數的存儲過程可以直接用 select
程序：
public String generateCode(String a1) {
Map<String,String> paramMap = new HashMap<String,String>();
paramMap.put("a1", a1);
SqlSession sqlSession = getSqlSession();
String result = sqlSession.selectOne("f1", paramMap);
return result;
}
2.使用OUT參數的存儲過程：
創建存儲過程：
create or replace function testproc(a1 varchar(32),out a2 varchar(32),out a3 varchar(32)) as $$
declare
begin
select id into a2 from t_project where id=a1;
select name into a3 from t_project where id=a1;
return;
end;
$$
language plpgsql;
sqlMap配置文件：
<select id="轎余generateCode1" parameterType="map" statementType="CALLABLE" useCache="false">
{
call testproc(
#{a1,mode=IN,jdbcType=VARCHAR},
#{a2,mode=OUT,jdbcType=VARCHAR},
#{a3,mode=OUT,jdbcType=VARCHAR}
)
}
</select>
程序：
public Map generateCode1(String a1) {
Map<String,String> paramMap = new HashMap<String,String>();
paramMap.put("a1", k1);
SqlSession sqlSession = getSqlSession();
sqlSession.selectOne("generateCode1", paramMap);
return paramMap;
}
帶輸出參數的存儲過程，sqlSession.selectOne("generateCode1", paramMap);
將paramMap傳入之後mybatis調用存儲過程，閉慎滾將paramMap進行填充
paramMap最後的值：{a1=R20148800900, a2=R20148800900, a3=項目名稱}
㈤ 存儲過程參數不同時,名字可以相同不
這種掘納悉情況， 某些資料庫可以， 某些資料庫不行。
例如 Postgrsql 是可以的。
下面是例子代碼
PostgreSQL不直接支持參數的默認值。
但是支持重載。
Test=#TRUNCATETABLEtest_helloworld;
TRUNCATETABLETest=#(
Test(#p_user_nameVARCHAR,
Test(#p_val1VARCHAR,
Test(#p_val2VARCHAR)RETURNSvoidAS
Test-#$$
Test$#BEGIN
Test$#INSERTINTOtest_helloworld(data)
Test$#VALUES(p_user_name||p_val1||判乎p_val2);
Test$#END;
Test$#$$
Test-#LANGUAGEplpgsql;
CREATEFUNCTION
Test=#
Test=#(
Test(#p_user_nameVARCHAR,
Test(#p_val1VARCHAR)RETURNSvoidAS
Test-#$$
Test$#BEGIN
Test$#PERFORMHelloWorld3(p_user_name,p_val1,'XYZ');
Test$#END;
Test$#茄滲$$
Test-#LANGUAGEplpgsql;
CREATEFUNCTION
Test=#
Test=#(
Test(#p_user_nameVARCHAR)RETURNSvoidAS
Test-#$$
Test$#BEGIN
Test$#PERFORMHelloWorld3(p_user_name,'OPQ');
Test$#END;
Test$#$$
Test-#LANGUAGEplpgsql;
CREATEFUNCTION
Test=#Test=#SELECTHelloWorld3('ABC');
helloworld3
-------------
(1行記錄)
Test=#select*fromtest_helloworld;
data
--------------
ABCOPQXYZ
(1行記錄)
Test=#
㈥ 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中創建帶游標的存儲過程（function)
在postgresql中創建帶游標的存儲過程（function)
一般在plsql中執行存儲過程的方式有兩種，一種是帶參數的 begin 存儲過程名(參數1,參數2...); end; 另一種是不帶參數的 begin 存儲過程名; end; 注意標點的寫法，都是英文寫法的
㈧ postgresql 存儲過程的事務處理
一個PostgreSQL存儲過程的例子
需求:
給出如下條件進行批處理編排
- 開始日期時間
- 重復間隔(分鍾)
- 重復次數
要求在檔期內重復安排節目播出, 比如: 2003.01.01 08:00 開始每隔240分鍾播出一次, 一共播出100次
資料庫表格(CO_SCHEDULE)
------------------------------
N_PROGID INT
DT_STARTTIME TIMESTAMP
DT_ENDTIME TIMESTAMP
存儲過程的實現：
create table co_schele(n_progid int,dt_starttime timestamp,dt_endtime timestamp);
//創建函數：
create function add_program_time(int4,timestamp,int4,int4,int4) returns bool as '
declare
prog_id alias for $1;
ration_min alias for $3;
period_min alias for $4;
repeat_times alias for $5;
i int;
starttime timestamp;
ins_starttime timestamp;
ins_endtime timestamp;
begin
starttime :=$2;
i := 0;
while i<repeat_times loop
ins_starttime := starttime;
ins_endtime := timestamp_pl_span(ins_starttime,ration_min || ''mins'');
starttime := timestamp_pl_span(ins_starttime,period_min || ''mins'');
insert into co_schele values(prog_id,ins_starttime,ins_endtime);
i := i+1;
end loop;
if i<repeat_times then
return false;
else
return true;
end if;
end;
'language 'plpgsql';
//執行函數：
select add_program_time(1,'2002-10-20 0:0:0','5','60','5');
//查看結果：select * from co_schele;
n_progid | dt_starttime | dt_endtime
----------+------------------------+------------------------
1 | 2002-10-20 00:00:00+08 | 2002-10-20 00:05:00+08
1 | 2002-10-20 01:00:00+08 | 2002-10-20 01:05:00+08
1 | 2002-10-20 02:00:00+08 | 2002-10-20 02:05:00+08
1 | 2002-10-20 03:00:00+08 | 2002-10-20 03:05:00+08
1 | 2002-10-20 04:00:00+08 | 2002-10-20 04:05:00+08
ps:
1.資料庫一載入 plpgsql語言。如沒有，
su - postgres
createlang plpgsql dbname
2.至於返回類型為bool,是因為我不知道如何讓函數不返回值。等待改進。
㈨ PostgreSQL Oracle 兼容性之存儲過程
在oracle中，函數和存儲過程是經常使用到的，並且有所區別；而postgresql中函數和存儲過程都是相同定義的。
1.定義：定義存儲過程的關鍵字為procere。
2.創建存儲過程
例：
（無參數）
（有參有返）
總結 ：
1.創建存儲過程的關鍵字為procere。
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.存儲過程定義關鍵字用procere，函數定義用function。
2.存儲過程中不能用return返回值，但函數中可以，而且函數中必須有return子句。
3.執行方式略有不同，存儲過程的執行方式有兩種（1.使用execute2.使用begin和end），函數除了存儲過程的兩種方式外，還可以當做表達式使用，例如放在select中（select f1() form al;）。
postgresql則將函數和存儲過程合為一體，不再明確區分存儲過程與函數。
1.定義：定義函數（存儲過程 ）的關鍵字為function。
2.創建
例：
1.必須有有returns（注意是returns不是return）子句，無返回值時returns viod
2.執行時，有返回值用select，無返回值時用perform
3.必須指定語言LANGUAGE
㈩ postgresql如何寫存儲過程！急急急
create OR REPLACE FUNCTION test()
RETURNS int4 AS
$BODY$
declare
r RECORD;
num int4 := 0;
sql "varchar" := 'select f_data_type from t_water_arguments group by f_data_type having count(*) >= 2 limit 1';
begin
FOR r IN EXECUTE sql LOOP
num = r.f_data_type;
end LOOP;
return num;
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
select test();
http://bianbian.org/technology/52.html
裡面跡逗有比較姿頌賣詳細的介紹櫻慧