这篇文章主要介绍了PostgreSQL存储过程用法,结合具体实例详细分析了PostgreSQL数据库存储过程的定义、使用方法及相关操作注意事项,并附带一个完整实例供大家参考,需要的朋友可以参考下

本文实例讲述了postgreSQL存储过程用法。分享给大家供大家参考,具体如下:

转了N多的SQL语句,可是自己用时,却到处是坑啊,啊,啊!!!!!!!!!!!!!!!

想写一个获取表中最新ID值.

上代码

CREATE TABLE department(
  ID INT PRIMARY KEY             NOT NULL,
  d_code                   VARCHAR(50),
  d_name                   VARCHAR(50)   NOT NULL,
  d_parentID                 INT       NOT NULL DEFAULT 0
);
--insert into department values(1,'001','office');
--insert into department values(2,'002','office',1);

下面要写个存储过程,以获取表中ID的最大值:

drop function f_getNewID(text,text);
create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$
declare
  mysql text;
    myID integer;
begin
  mysql:='select max( $1 ) from $2';
    execute mysql into myID using myFeildName,myTableName;
  if myID is null or myID=0 then return 1;
  else return myID+1;
   end if;
end;
$$ language plpgsql;
--大家可以试一下,上面这个是会报错的
--select f_getNewID('department','ID');
--出错!

看了官方文档,人家就是这么用的:

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
  INTO c
  USING checked_user, checked_date;

你确定你看清楚了?????

确定你读完读懂了说明书?????

--这个看了?
---------------------------------------
EXECUTE 'SELECT count(*) FROM '
  || quote_ident(tabname)
  || ' WHERE inserted_by = $1 AND inserted <= $2'
  INTO c
  USING checked_user, checked_date;

--这个看了?
---------------------------------------
EXECUTE 'UPDATE tbl SET '
    || quote_ident(colname)
    || ' = '
    || quote_literal(newvalue)
    || ' WHERE key = '
    || quote_literal(keyvalue);
--=============================
--好吧, 我改
------------------------------------------------------
drop function f_getNewID(text,text);
create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$
declare
  mysql text;
  myID integer;
begin
  mysql:='select max('
    || quote_ident(myFeildName)
    ||') from '
    || quote_ident(myTableName);
  execute mysql into myID;
  --using myTableName,myFeildName;

  if myID is null or myID=0 then return 1;
  else return myID+1;
   end if;
end;
$$ language plpgsql;
--==============================
--漂亮,成功了!
--But Why?
--注意 对象(表名、字段名等)是不可以直接用变量的,要用 quote_ident()
-------------------------------------------------------
postgres=# select f_getnewid('department','ID');
--错误: 字段 "ID" 不存在
--第1行select max("ID") from department
        ^
--查询: select max("ID") from department
--背景: 在EXECUTE的第10行的PL/pgSQL函数f_getnewid(text,text)

--===============================
--什么情况,ID怎么会有双引号,引号,号,号???
----------------------------------------------------------
--这里要感谢大神:权宗亮@飞象数据
--改成这样:
postgres=# select f_getnewid('department','id');
 f_getnewid
------------
     2
(1 行记录)
----终于成功了!大小写还有区别吗??? --but why? --当在命令行输入
CREATE TABLE role(
  ID                     INT PRIMARY KEY NOT NULL,
  r_name                   VARCHAR(50)   NOT NULL,
  r_paretnID                 INT       NOT NULL  DEFAULT 0
);
--结果在pgAdmin里看到的却是小写的

--同样,如果是在QUERY TOOLS 下用这样的语句创建还是 所有的字体名为小写
--如果我就想大写怎么办????
--要这样写
CREATE TABLE "RoleUPER"(
  "ID"                     INT PRIMARY KEY NOT NULL,
  r_name                   VARCHAR(50)   NOT NULL,
  "r_paretnID"                 INT       NOT NULL  DEFAULT 0
);
--再用大象看看

可以了!

总结一下:

1、存储过程(FUNCITON)变量可以直接用  || 拼接。上面没有列出,下面给个栗子:

create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$
declare
  mysql text;
    myID integer;
begin
  mysql:='select max('|| $2 || ' ) from '||$1;
    execute mysql into myID using myFeildName,myTableName;
  if myID is null or myID=0 then return 1;
  else return myID+1;
   end if;
end;
$$ language plpgsql;

2、存储过程的对象不可以直接用变量,要用 quote_ident(objVar)

3、$1  $2是 FUNCTION 参数的顺序,如1中的 $1 $2交换,USING 后面的不换 结果 :

select max(myTableName) from myFeildname

4、注意:SQL语句中的大写全部会变成小写,要想大写存大,必须要用双引号。

附:一个完整postgreSQL 存储过程示例

CREATE OR REPLACE FUNCTION mt_f_avl_oee_period(i_station character varying,i_type int)
 RETURNS integer AS
$BODY$
DECLARE
 v_start_hour character varying;
 v_end_hour character varying;
 v_start_time character varying;
 v_end_time character varying;
 v_start_datetime timestamp;
 v_end_datetime timestamp;
 v_type int := 0;
 v_rtn int;
/*
 v_test9_count int;
 v_test9_success int;
 v_runningtime double precision;
 v_availablerate double precision;
 */
BEGIN
-- hour = even, minute > 30
-- exists
--
if i_type = 1 then
SELECT EXTRACT(HOUR FROM CURRENT_TIME - interval '2 hours' ) into v_start_hour;  --two hours ago
SELECT EXTRACT(HOUR FROM CURRENT_TIME ) into v_end_hour;  --get 'hour' of current time
select v_start_hour || ':30' into v_start_time;
select v_end_hour || ':30' into v_end_time;
select mt_f_avl_oee_period_e(i_station, to_char( CURRENT_Date, 'YYYY-MM-DD'), v_start_time, v_end_time) into v_rtn;
else
SELECT EXTRACT(HOUR FROM CURRENT_TIME - interval '3 hours' ) into v_start_hour;
SELECT EXTRACT(HOUR FROM CURRENT_TIME ) into v_end_hour;
select v_start_hour || ':30' into v_start_time;
select v_end_hour || ':00' into v_end_time;
select mt_f_avl_oee_period_midnight(i_station, to_char( CURRENT_Date, 'YYYY-MM-DD'), v_start_time, v_end_time) into v_rtn;
end if;
RETURN 1;
EXCEPTION
WHEN others THEN
  RAISE;
RETURN 0;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
ALTER FUNCTION mt_f_avl_oee_period(i_station character varying,i_type int)
 OWNER TO postgres;

希望本文所述对大家PostgreSQL程序设计有所帮助。

最新资讯
5G新标准补齐“能力三角” 工业互联网加速启动

5G新标准补齐“能力三

随着全球首个以物联网场景为主的5G标准R16的冻结,工业
捷足先登“拿下”神州租车:上汽大出行版图已现

捷足先登“拿下”神州

上汽香港拟以每股3.10港币的价格以现金出资方式收购神
中芯国际回A倒计时:科创板募资或超500亿元

中芯国际回A倒计时:科

对于中芯国际的发行价,业内此前多有讨论,并普遍参考科创
5G R16标准冻结  B端应用大幕开启

5G R16标准冻结 B端

国际标准组织3GPP冻结了5G R16标准,而R16着重对高可靠
中芯国际上市交易倒计时 网上路演聚焦未来成长性

中芯国际上市交易倒计

在网上投资者交流会上,中芯国际与投资者就该公司业务状
小电抢跑共享充电宝第一股

小电抢跑共享充电宝第

共享充电宝服务商“小电科技”(以下简称“小电”)已同浙
最新文章
pgsql查询优化之模糊查询实例详解

pgsql查询优化之模糊

这篇文章主要给大家介绍了关于pgsql查询优化之模糊查
Ubuntu PostgreSQL安装和配置的介绍

Ubuntu PostgreSQL安

今天小编就为大家分享一篇关于Ubuntu PostgreSQL安装
PostgreSQL实现一个通用标签系统

PostgreSQL实现一个通

这篇文章主要给大家介绍了关于利用PostgreSQL实现一个
PostgreSQL中使用数组改进性能实例代码

PostgreSQL中使用数组

这篇文章主要给大家介绍了关于PostgreSQL中使用数组改
Postgresql主从异步流复制方案的深入探究

Postgresql主从异步流

这篇文章主要给大家介绍了关于Postgresql主从异步流复
PostgreSQL存储过程用法实战详解

PostgreSQL存储过程用

这篇文章主要介绍了PostgreSQL存储过程用法,结合具体