[懒人必备]postgresql通用单表增删改查存储过程

本文的目的在于解决web开发也好,系统开发也好那60%左右的单表增删改查的问题,

至于多表联查---相信我,自己手写sql比什么都强.

ps:我这里提一下,那就是类型转换的问题以及json取值问题

这是下面这些文章没有想过或者没有解决的问题.

1/类型转换问题

sql命令有几种默认转换关系.如果是timestamp,那么写 &＃39;2010-10-20&＃39;这种格式的字符或默认转换为timestamp日期,不需要额外转的.

但是,如果字段是 int,bigint,float,double,decimal这些数字,那么postgresql里面是不会自动将字符串转为对应类型,到时候会报错,说类型不对建议转换类型什么的.

2/json取值问题

在postgresql的存储过程里面可以传入json数据---json格式字符串也可以直接转为json数据,它有两种取值方式,

譬如对于:

{"word":"明珠斗士","createTime":1582081087851}

来说,

可以通过:

json->&＃39;createTime&＃39; 返回类型 json
json->>&＃39;createTime&＃39; 返回类型 text

来取值,但是留意到没有?就是postgresql返回来的取值 没有做类型适配,但你要执行:

update table01 set "createTime"&＃61;json->&＃39;createTime&＃39;的时候

无论用哪一种取值方式都会提示错误的.所以,在写通用update存储过程时候必须要考虑到类型适配问题.

参考自:

https://www.cnblogs.com/ssqhan/p/7399789.html

其中该文章提及到一个存储过程:

create or replace function f_update_all(tablename          text,      update_feilds      text,       condition_feilds   text,       out return_value   text        
) as $$
declareex_sql             text;recs               record;_key               text ;_value             text;
beginex_sql:&＃61;&＃39;update &＃39;||quote_ident(tablename)||&＃39; set &＃39;;--setting values for updated tablefor recs in select * from json_array_elements(update_feilds::json)   loop_key   :&＃61; recs.value ->> &＃39;feild_name&＃39;;_value :&＃61; recs.value ->> &＃39;feild_value&＃39; ;if json_typeof(recs.value -> &＃39;feild_value&＃39;) &＃61;&＃39;number&＃39; then ex_sql:&＃61;ex_sql|| _key || &＃39;&＃61;&＃39; ||  _value ||&＃39;,&＃39;;else ex_sql:&＃61;ex_sql|| _key || &＃39;&＃61;&＃39;&＃39;&＃39;||  (recs.value ->> &＃39;feild_value&＃39;)  || &＃39;&＃39;&＃39;,&＃39;;end if;end loop;ex_sql:&＃61; substring(ex_sql from 0 for length(ex_sql));--setting condition in where ex_sql:&＃61;ex_sql||&＃39; where 1&＃61;1&＃39;;for recs in select * from  json_array_elements(condition_feilds::json)  loop_key   :&＃61; recs.value ->> &＃39;feild_name&＃39;;_value :&＃61; recs.value ->> &＃39;feild_value&＃39; ;if json_typeof(recs.value -> &＃39;feild_value&＃39;) &＃61;&＃39;number&＃39; then ex_sql:&＃61;ex_sql|| &＃39; and &＃39; || _key || &＃39;&＃61;&＃39; ||  _value ||&＃39;,&＃39;;else ex_sql:&＃61;ex_sql|| &＃39; and &＃39; || _key || &＃39;&＃61;&＃39;&＃39;&＃39;||  (recs.value ->> &＃39;feild_value&＃39;) || &＃39;&＃39;&＃39;,&＃39;;end if;end loop;ex_sql:&＃61; substring(ex_sql from 0 for length(ex_sql));return_value:&＃61;ex_sql;
end;
$$ language plpgsql;

重点是这个存储过程是用了jsonarray遍历然后获得字段名称以及字段值的,可以在此基础上进行改造.

参考2:

https://blog.csdn.net/kmblack1/article/details/82704844

里面提到的存储过程是:

drop function if exists exe_dynamic_sql(bigint);
drop function if exists exe_dynamic_count(bigint);
--返回记录集
create or replace function exe_dynamic_sql(ival bigint)returns table(objectid bigint,name varchar(128))
as $$declarebeginreturn query execute &＃39;select objectid,name from dictionarys where parentid&＃61;$1 order by parentid,sort&＃39; using $1; end;
$$ language plpgsql;--赋值给变量
create or replace function exe_dynamic_count(ival bigint)returns bigint
as $$declarev_count bigint;beginexecute &＃39;select count(*) from dictionarys where parentid&＃61;$1&＃39; using $1 into v_count;return v_count;end;
$$ language plpgsql;
--测试
select * from exe_dynamic_sql(26);
select exe_dynamic_count(26);

可以看到这里是需要使用到参数 $1 这种形式,可以避免sql注入.

下面就是通用的存储过程,上面都有注解,基本上这些都是在实际生产环境中使用过的,插入记录与更新记录的字段参数使用的是json格式的字符串数据,

想必对于任何一种语言都不是大问题, 而搜索出来的列表也是直接返回的是json格式的字符串数据,也符合规范.

当然,使用的是动态sql语句,在自定where条件语句搜索列表时候,不要作死使用用户输入的任何数据.

/***
* 单表crud通用存储过程
* 单表记录更新.
* &＃64;param tablename 表名称
* &＃64;param updateparameter 需要更新的字段以及对应的value值,注意,请使用json格式字符串,例如:{"column1":"value4t1","column2":"value4t2","birthday":12548902}
* &＃64;param whereCondition  where 条件只能是内部人员使用,不能开放给客户端! 例子: where id&＃61; 1;
* &＃64;param out status_code output类型数据,0 表示 失败,1表示成功,-1 表示发生异常.
* &＃64;param out return_message output类型数据, 这是提示,譬如,当tableName为空的时候会提示"tableName不能为空"
**/
create or replace function common_proc_update_record(tablename          varchar(100),updateparameter      varchar, -- 注意,直接使用json字符串--key-value形式即可,例如:{"column1":"value4t1","column2":"value4t2"}whereCondition   varchar, -- where 条件只能是内部人员使用,不能开放给客户端! 例子: where id&＃61; 1;out status_code int ,-- 0 表示 失败,1表示成功,-1 表示发生异常.out return_message   varchar --
) as $$
declareex_sql             text;recs               record;_key               text ;declare _whereCondition varchar;declare _smallletter_where varchar;declare _paras varchar;declare _tableName varchar;declare item_key varchar;item_value json;loopIndex int;_tmpParaHolder varchar;_paras_values jsonb;-- 字段的值都放到这里.json_value_type varchar;
begin-- 设置中国时区set time zone &＃39;PRC&＃39;;_paras_values:&＃61;(updateparameter)::jsonb;_whereCondition&＃61;coalesce(whereCondition,&＃39;&＃39;);_whereCondition:&＃61;trim(_whereCondition);_paras:&＃61;trim(coalesce(updateparameter,&＃39;&＃39;));_tableName:&＃61;trim(coalesce(tablename,&＃39;&＃39;));if char_length(_tableName) <&＃61;0 thenstatus_code:&＃61;-1;return_message:&＃61;&＃39;未知表名!&＃39;;return;end if;-- 大小写敏感.-- --pg的字符串位置是从1开始的.
--     if position(&＃39;"&＃39; in _tableName) < 1 then
--       _tableName:&＃61;&＃39;"&＃39;||_tableName||&＃39;"&＃39;;
--     end if;
if _whereCondition is null or char_length(_whereCondition) <&＃61; 0 then
status_code:&＃61;-1;
return_message:&＃61;&＃39;请明确需要更新哪一条数据记录!&＃39;;
return;
end if;
if char_length(_paras) <&＃61;0 thenstatus_code:&＃61;-1;return_message:&＃61;&＃39;请明确需要更新的字段列表以及对应的值的json字符串.&＃39;;return;end if;
_smallletter_where:&＃61;lower(_whereCondition);
-- 检查一下where关键字是不是在条件语句,如果不在,就补充where 关键字. --pg的字符串位置是从1开始的.
if position(&＃39;where &＃39; in _smallletter_where) <> 1 then_whereCondition:&＃61;&＃39;where &＃39;||_whereCondition;
end if;ex_sql:&＃61;&＃39;update &＃39;||quote_ident(_tableName) || &＃39; set &＃39;;
loopIndex:&＃61;0;
-- 好了,遍历json里面的键值对.for item_key,item_value in select * from json_each(updateparameter::json)   looploopIndex:&＃61;coalesce(loopIndex,0)&＃43;1;_key:&＃61;trim(item_key);json_value_type:&＃61;json_typeof(item_value);-- item_value 在使用->引用的时候是json/jsonb类型,在使用 ->>引用的时候是text类型,-- 无论哪一个直接用来update或者insert字段都会提示 错误,叫你cast转换类型的,下面就直接判断是什么类型,然后-- 自动转换.-- 类型有 string,-- number 对应于:numeric-- ps:--json字符串最经常的就两种,string和数字,其他的以后看到了再考虑,譬如,数组如何处理.if position(&＃39;"&＃39; in _key ) < 1 then_key:&＃61; format(&＃39;"%s"&＃39;,_key);-- &＃39;"&＃39;||_key||&＃39;"&＃39;;end if;if json_value_type&＃61;&＃39;number&＃39; then_tmpParaHolder:&＃61; format(&＃39;($1->>&＃39;&＃39;%s&＃39;&＃39;)::numeric&＃39; ,trim(item_key));else_tmpParaHolder:&＃61; format(&＃39;($1->>&＃39;&＃39;%s&＃39;&＃39;)&＃39; ,trim(item_key));end if;-- &＃39;$&＃39;||cast(loopIndex as varchar);-- 参数的占位符号.ex_sql:&＃61;format(&＃39;%s %s&＃61;%s,&＃39;,ex_sql,_key,_tmpParaHolder);
--       ex_sql:&＃61;ex_sql|| _key || &＃39;&＃61;&＃39; ||  _tmpParaHolder ||&＃39;,&＃39;;
--       _tmpJsonItem:&＃61;&＃39;{}&＃39;;
--       _tmpJsonItem[&＃39;value&＃39;]:&＃61;(item_value);raise notice &＃39;已经将 相关 item value 读取出来了:%,类型是:%&＃39;,item_value,json_typeof(item_value);end loop;
-- 去掉最后一个逗号.
ex_sql:&＃61;substr(ex_sql,0,char_length(ex_sql));
ex_sql:&＃61; ex_sql||&＃39; &＃39;|| _whereCondition;raise  notice  &＃39;动态sql是:%&＃39;,ex_sql;
-- raise notice  &＃39;值的数组为:%&＃39;,_paras_values;-- 执行sql
-- dynamic_execute_with_paras:&＃61; &＃39;execute ex_sql &＃39;;
-- execute  dynamic_execute_with_paras;execute  ex_sql using _paras_values;
-- exec &＃39;execute ex_sql using _paras_values&＃39;;
status_code:&＃61;1;
return_message:&＃61;&＃39;&＃39;;
--   execute &＃39;select count(*) from dictionarys where parentid&＃61;$1&＃39; using $1 into v_count;end;
$$ language plpgsql;/***
* 单表crud通用存储过程
* 单表记录添加.
* &＃64;param tablename 表名称
* &＃64;param insertparas 需要添加的字段以及对应的value值,注意,请使用json格式字符串,例如:{"column1":"value4t1","column2":"value4t2","birthday":12548902}
* &＃64;param fetchRecordByAutoIncrementPrimaryKeyName  是否有自增主键,如果有自增主键譬如:userid,那么就将userid传过来,
* --然后系统会在插入记录之后,试获取最近一条记录,在return_autopk_record会返回新插入记录的json格式字符串.如果没有的话就不进行处理.
* &＃64;param out status_code output类型数据,0 表示 失败,1表示成功,-1 表示发生异常.
* &＃64;param out return_message output类型数据, 这是提示,譬如,当tableName为空的时候会提示"tableName不能为空"
* &＃64;param out return_autopk_record 在给定了自增主键以后,系统将尝试获取当前插入的自增主键对应的记录,然后json格式化赋值给该参数.
**/
create or replace function common_proc_insert_record(tablename          varchar(100),insertparas      varchar, -- 注意,直接使用json字符串--key-value形式即可,例如:{"column1":"value4t1","column2":"value4t2"}fetchRecordByAutoIncrementPrimaryKeyName varchar,-- 是否有自增主键,如果有自增主键譬如:userid,那么就将userid传过来,然后系统会在插入记录之后-- 尝试获取最近一条记录,在return_autopk_record会返回新插入记录的json格式字符串.如果没有的话就不进行处理.out status_code int ,-- 0 表示 失败,1表示成功,-1 表示发生异常.out return_message   varchar, --,out return_autopk_record varchar --
)as $$
declareex_sql             text;recs               record;_key               text ;declare _paras varchar;declare _tableName varchar;declare item_key varchar;item_value json;loopIndex int;_tmpParaHolder varchar;_paras_values jsonb;-- 字段的值都放到这里.json_value_type varchar;_arr_sql_columns varchar[];_arr_sql_vals varchar[];_auto_icr_pkey varchar;
begin-- 设置中国时区.set time zone &＃39;PRC&＃39;;_auto_icr_pkey:&＃61;trim(coalesce(fetchRecordByAutoIncrementPrimaryKeyName));_paras_values:&＃61;(insertparas)::jsonb;_paras:&＃61;trim(coalesce(insertparas,&＃39;&＃39;));_tableName:&＃61;trim(coalesce(tablename,&＃39;&＃39;));if char_length(_tableName) <&＃61;0 thenstatus_code:&＃61;-1;return_message:&＃61;&＃39;未知表名!&＃39;;return;end if;-- 大小写敏感.-- --pg的字符串位置是从1开始的.
--     if position(&＃39;"&＃39; in _tableName) < 1 then
--       _tableName:&＃61;&＃39;"&＃39;||_tableName||&＃39;"&＃39;;
--     end if;
if char_length(_paras) <&＃61;0 thenstatus_code:&＃61;-1;return_message:&＃61;&＃39;请明确需要字段列表以及对应的值的json字符串.&＃39;;return;end if;ex_sql:&＃61;&＃39;insert into  &＃39;||quote_ident(_tableName) || &＃39; &＃39;;
loopIndex:&＃61;0;
-- 好了,遍历json里面的键值对.for item_key,item_value in select * from json_each(insertparas::json)   looploopIndex:&＃61;coalesce(loopIndex,0)&＃43;1;_key:&＃61;trim(item_key);json_value_type:&＃61;json_typeof(item_value);-- item_value 在使用->引用的时候是json/jsonb类型,在使用 ->>引用的时候是text类型,-- 无论哪一个直接用来update或者insert字段都会提示 错误,叫你cast转换类型的,下面就直接判断是什么类型,然后-- 自动转换.-- 类型有 string,-- number 对应于:numeric-- ps:--json字符串最经常的就两种,string和数字,其他的以后看到了再考虑,譬如,数组如何处理.if position(&＃39;"&＃39; in _key ) < 1 then_key:&＃61; format(&＃39;"%s"&＃39;,_key);-- &＃39;"&＃39;||_key||&＃39;"&＃39;;end if;if json_value_type&＃61;&＃39;number&＃39; then_tmpParaHolder:&＃61; format(&＃39;($1->>&＃39;&＃39;%s&＃39;&＃39;)::numeric&＃39; ,trim(item_key));else_tmpParaHolder:&＃61; format(&＃39;($1->>&＃39;&＃39;%s&＃39;&＃39;)&＃39; ,trim(item_key));end if;_arr_sql_columns:&＃61;_arr_sql_columns||(_key)::varchar;_arr_sql_vals:&＃61;_arr_sql_vals||(_tmpParaHolder)::VARCHAR;raise notice &＃39;已经将 相关 item value 读取出来了:%,类型是:%&＃39;,item_value,json_typeof(item_value);end loop;raise  notice &＃39;数组 columns:%&＃39;,array_to_string(_arr_sql_columns,&＃39;|&＃39;);raise notice &＃39;数组 参数:%&＃39;,array_to_string(_arr_sql_vals,&＃39;|&＃39;);
ex_sql:&＃61;ex_sql||format(&＃39; (%s) &＃39;,array_to_string(_arr_sql_columns,&＃39;,&＃39;));
ex_sql:&＃61;ex_sql||format(&＃39; values (%s) ; &＃39;,array_to_string(_arr_sql_vals,&＃39;,&＃39;));
-- 去掉最后一个逗号.raise  notice  &＃39;动态sql是:%&＃39;,ex_sql;
execute  ex_sql using _paras_values;-- 判断有没有影响行数if FOUND thenstatus_code:&＃61;1;return_message:&＃61;&＃39;成功添加记录&＃39;;elsestatus_code:&＃61;-1;return_message:&＃61;&＃39;无法添加数据记录!&＃39;;return;end if;
-- 判断是不是需要获取自增主键然后返回当前记录的json格式数据.
if char_length(_auto_icr_pkey)>0 thenif position(&＃39;"&＃39; in _auto_icr_pkey) < 1 then_auto_icr_pkey:&＃61;format(&＃39;"%s"&＃39;,_auto_icr_pkey);end if;-- 获取自增数据的记录.
--   currval(pg_get_serial_sequence(&＃39;"s_praise"&＃39;, &＃39;praiseId&＃39;));
--   rv_recordId:&＃61;currval(pg_get_serial_sequence(&＃39;"s_msg"&＃39;, &＃39;msgId&＃39;));-- 好了,再构造一个动态语句.ex_sql&＃61;format(&＃39;select row_to_json(t)  from (select * from %s where  %s&＃61;currval(pg_get_serial_sequence(&＃39;&＃39;%s&＃39;&＃39;, &＃39;&＃39;%s&＃39;&＃39;))) t&＃39;,quote_ident(_tableName),_auto_icr_pkey,quote_ident(_tableName),trim(fetchRecordByAutoIncrementPrimaryKeyName));raise notice &＃39;获取自增记录的动态sql是:%&＃39;,ex_sql;execute ex_sql into return_autopk_record;
end if;end;
$$ language plpgsql;/***
* 单表crud通用存储过程
* 单表条件搜索
* [按相等条件搜索] ps:这个存储过程用于搜索相同字段条件的记录列表,譬如: name&＃61;&＃39;t1&＃39; id&＃61;5 通常用于确定的,包含有主键的记录.
* &＃64;param tablename 表名称
* &＃64;param equalConditionJson 搜索条件,json格式字符串,例如:{"userId":21458},或者{"wxOpenId":"dfdfsdfdsfd"} 等,也可以同时传递多个条件.
-- 条件为空字符串的话就是单纯搜索列表了
* &＃64;param orderByStr 排序条件语句. 例如: order by id desc 等等. 如果为空的话就不
* &＃64;param pageIndex 分页,页码,以1作为第一页
* &＃64;param pageSize 分页,每页限制多少条记录,默认为20.
* &＃64;param out status_code output类型数据,0 表示 失败,1表示成功,-1 表示发生异常.
* &＃64;param out return_message output类型数据, 这是提示,譬如,当tableName为空的时候会提示"tableName不能为空"
* &＃64;param out total_records 当前条件下面总共有多少条记录
* &＃64;param out return_list_json 系统在搜索过程中将结果集序列化为json字符串,赋值给这个参数.
**/
create or replace function common_proc_search_by_eqCnd(tablename          varchar(100),equalConditionJson     varchar, -- 注意,直接使用json字符串--key-value形式即可,例如:{"column1":"value4t1","column2":"value4t2"}orderByStr varchar,pageIndex int,pageSize int,out status_code int ,-- 0 表示 失败,1表示成功,-1 表示发生异常.out return_message   varchar, --,out total_records int,out return_list_json varchar --
)as $$
declare_key               text ;declare _paras varchar;declare _tableName varchar;declare item_key varchar;item_value json;loopIndex int;_tmpParaHolder varchar;_paras_values jsonb;-- 字段的值都放到这里.json_value_type varchar;_eq_json varchar;_order_by varchar;_sql_select_count varchar;_sql_select_list varchar;_arr_column_cnd varchar[];declare rv_offset int;declare rv_limit int;
begin-- 设置中国时区.set time zone &＃39;PRC&＃39;;rv_offset:&＃61;1;rv_limit:&＃61;20;_tableName:&＃61;trim(coalesce(tablename,&＃39;&＃39;));if char_length(_tableName) <&＃61;0 thenstatus_code:&＃61;-1;return_message:&＃61;&＃39;未知表名!&＃39;;return;end if;status_code:&＃61;1;return_message:&＃61;&＃39;&＃39;;_eq_json:&＃61;trim(coalesce(equalConditionJson,&＃39;&＃39;));_order_by:&＃61;trim(coalesce(orderByStr));_sql_select_count:&＃61; format(&＃39;select count(*)  from %s &＃39;,quote_ident(_tableName));_sql_select_list:&＃61; format(&＃39;select *  from %s &＃39;,quote_ident(_tableName));if char_length(_eq_json) > 0 then-- 包含了 where conditionloopIndex:&＃61;0;for item_key,item_value in select * from json_each(_eq_json::json)   looploopIndex:&＃61;coalesce(loopIndex,0)&＃43;1;_key:&＃61;trim(item_key);json_value_type:&＃61;json_typeof(item_value);-- item_value 在使用->引用的时候是json/jsonb类型,在使用 ->>引用的时候是text类型,-- 无论哪一个直接用来update或者insert字段都会提示 错误,叫你cast转换类型的,下面就直接判断是什么类型,然后-- 自动转换.-- 类型有 string,-- number 对应于:numeric-- ps:--json字符串最经常的就两种,string和数字,其他的以后看到了再考虑,譬如,数组如何处理.if position(&＃39;"&＃39; in _key ) < 1 then_key:&＃61; format(&＃39;"%s"&＃39;,_key);-- &＃39;"&＃39;||_key||&＃39;"&＃39;;end if;if json_value_type&＃61;&＃39;number&＃39; then_tmpParaHolder:&＃61; format(&＃39;($1->>&＃39;&＃39;%s&＃39;&＃39;)::numeric&＃39; ,trim(item_key));else_tmpParaHolder:&＃61; format(&＃39;($1->>&＃39;&＃39;%s&＃39;&＃39;)&＃39; ,trim(item_key));end if;_arr_column_cnd:&＃61;_arr_column_cnd|| format(&＃39;%s&＃61;%s&＃39;,_key,_tmpParaHolder)::varchar;raise notice &＃39;已经将 相关 item value 读取出来了:%,类型是:%&＃39;,item_value,json_typeof(item_value);end loop;-- 将where 条件添加到后面._sql_select_count:&＃61;_sql_select_count || &＃39; where &＃39; ||(array_to_string(_arr_column_cnd,&＃39; and &＃39;)::varchar);_sql_select_list:&＃61; _sql_select_list || &＃39; where &＃39; || (array_to_string(_arr_column_cnd,&＃39; and &＃39;)::varchar);end if;if char_length(_order_by)>0 then-- 如果有order byif position(&＃39;order by &＃39; in lower(_order_by)) < 1 then-- 开头并无order by 关键字,添加上去._order_by:&＃61;&＃39;order by &＃39;||_order_by;end if;--     _sql_select_list:&＃61;format()_sql_select_list:&＃61;_sql_select_list||&＃39; &＃39;||_order_by;end if;-- 计算分页.if pageSize is null or pageSize < 1  thenrv_limit :&＃61; 20;elserv_limit :&＃61; pageSize;end if;if rv_limit > 1000 thenrv_limit:&＃61;1000;end if;if pageIndex is null or pageIndex < 1 thenrv_offset :&＃61; 0;elserv_offset :&＃61; (pageIndex - 1) * rv_limit;end if;-- sql list &＃61;&＃61;&＃61;   select array_to_json(array_agg(row_to_json(___tbl_middle))) into list_result from ( xxxx limit 20 offset 0 ) as ___tbl_middle ;_sql_select_list:&＃61;format(&＃39;select array_to_json(array_agg(row_to_json(___tbl_middle)))  from ( %s limit %s offset %s ) as ___tbl_middle ;&＃39;,_sql_select_list,rv_limit,rv_offset);raise  notice  &＃39;获取总数量的的sql:%&＃39;,_sql_select_count;
raise  notice  &＃39;获取列表的的sql:%&＃39;,_sql_select_list;execute _sql_select_count using (_eq_json)::json into total_records;
execute _sql_select_list using (_eq_json)::json into return_list_json;end;
$$ language plpgsql;/***
* 单表crud通用存储过程
* 单表条件搜索
* [自由构造条件语句进行搜索] ps:这个存储过程用里面的条件不能使用任何客户端输入的数据,切记了.
* &＃64;param tablename 表名称
* &＃64;param whereCondition 搜索条件,自行构建语句: 譬如: where id&＃61;1
* &＃64;param orderByStr 排序条件语句. 例如: order by id desc 等等. 如果为空的话就不
* &＃64;param pageIndex 分页,页码,以1作为第一页
* &＃64;param pageSize 分页,每页限制多少条记录,默认为20.
* &＃64;param out status_code output类型数据,0 表示 失败,1表示成功,-1 表示发生异常.
* &＃64;param out return_message output类型数据, 这是提示,譬如,当tableName为空的时候会提示"tableName不能为空"
* &＃64;param out total_records 当前条件下面总共有多少条记录
* &＃64;param out return_list_json 系统在搜索过程中将结果集序列化为json字符串,赋值给这个参数.
**/
create or replace function common_proc_search_table(tablename          varchar(100),whereCondition     varchar, -- 注意,直接使用json字符串--key-value形式即可,例如:{"column1":"value4t1","column2":"value4t2"}orderByStr varchar,pageIndex int,pageSize int,out status_code int ,-- 0 表示 失败,1表示成功,-1 表示发生异常.out return_message   varchar, --,out total_records int,out return_list_json varchar --
)as $$
declare_key               text ;declare _paras varchar;declare _tableName varchar;declare item_key varchar;item_value json;loopIndex int;_tmpParaHolder varchar;_paras_values jsonb;-- 字段的值都放到这里.json_value_type varchar;_whereCondition varchar;_order_by varchar;_sql_select_count varchar;_sql_select_list varchar;_arr_column_cnd varchar[];declare rv_offset int;declare rv_limit int;
begin-- 设置中国时区.set time zone &＃39;PRC&＃39;;rv_offset:&＃61;1;rv_limit:&＃61;20;_tableName:&＃61;trim(coalesce(tablename,&＃39;&＃39;));if char_length(_tableName) <&＃61;0 thenstatus_code:&＃61;-1;return_message:&＃61;&＃39;未知表名!&＃39;;return;end if;status_code:&＃61;1;return_message:&＃61;&＃39;&＃39;;_whereCondition:&＃61;trim(coalesce(whereCondition,&＃39;&＃39;));_order_by:&＃61;trim(coalesce(orderByStr));_sql_select_count:&＃61; format(&＃39;select count(*)  from %s &＃39;,quote_ident(_tableName));_sql_select_list:&＃61; format(&＃39;select *  from %s &＃39;,quote_ident(_tableName));if char_length(_whereCondition) > 0 then-- 包含了 where conditionif position(&＃39;where &＃39; in lower(_whereCondition)) < 1 then_whereCondition:&＃61;&＃39;where &＃39;||whereCondition;end if;-- 将where 条件添加到后面._sql_select_count:&＃61;_sql_select_count || _whereCondition;_sql_select_list:&＃61; _sql_select_list || _whereCondition;end if;if char_length(_order_by)>0 then-- 如果有order byif position(&＃39;order by &＃39; in lower(_order_by)) < 1 then-- 开头并无order by 关键字,添加上去._order_by:&＃61;&＃39;order by &＃39;||_order_by;end if;--     _sql_select_list:&＃61;format()_sql_select_list:&＃61;_sql_select_list||&＃39; &＃39;||_order_by;end if;-- 计算分页.if pageSize is null or pageSize < 1  thenrv_limit :&＃61; 20;elserv_limit :&＃61; pageSize;end if;if rv_limit > 1000 thenrv_limit:&＃61;1000;end if;if pageIndex is null or pageIndex < 1 thenrv_offset :&＃61; 0;elserv_offset :&＃61; (pageIndex - 1) * rv_limit;end if;-- sql list &＃61;&＃61;&＃61;   select array_to_json(array_agg(row_to_json(___tbl_middle))) into list_result from ( xxxx limit 20 offset 0 ) as ___tbl_middle ;_sql_select_list:&＃61;format(&＃39;select array_to_json(array_agg(row_to_json(___tbl_middle)))  from ( %s limit %s offset %s ) as ___tbl_middle ;&＃39;,_sql_select_list,rv_limit,rv_offset);raise  notice  &＃39;获取总数量的的sql:%&＃39;,_sql_select_count;
raise  notice  &＃39;获取列表的的sql:%&＃39;,_sql_select_list;execute _sql_select_count  into total_records;
execute _sql_select_list  into return_list_json;end;
$$ language plpgsql;

假设我们现在有一张表:

其中id是自增主键, word是需要禁用的关键词,createTime是bigint类型,时间戳.

好了,下面就是实际使用的效果----这里直接用sql工具,就不演示具体语言如何调用存储过程,如何获取 output的参数了.

添加记录---将会在添加完成时候,如果有指定自增主键,那么就会自动返回最近一次的添加记录.

在日志控制台上面可以看到日志记录,里面包含了动态构造的sql语句----注意,里面使用的是json参数, $1 就是 &＃39;{"word":"","createTIme":5455455}&＃39;这个参数.

查询刚才添加的记录,可以使用id&＃61;xxx,或者直接查询相等的条件的记录,如下:

可以看到返回的是数组形式的json字符串,里面的id是3,那么,根据这个id的条件来进行更新操作:

最后,获取所有记录:

一个很基本的增删改查通用存储过程就是这样,至于其他复杂的功能,譬如,postgresql里面如何添加数组之类的, 就要进一步优化了.