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
里面迹逗有比较姿颂卖详细的介绍樱慧