这篇文章主要介绍了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程序设计有所帮助。

最新资讯
蔚来汽车李斌:山村里的大学生,创新大潮中的创业者

蔚来汽车李斌:山村里的

从山村里走出来的大学生,到创业创新大潮中的活跃面孔乃
刘作虎:手机行业波涛汹涌 要坚持商业本质相信时间

刘作虎:手机行业波涛汹

一加手机今日举行一加五周年特别活动。一加科技CEO刘
直击|阿里巴巴将试水知产服务市场 推知产开放平台

直击|阿里巴巴将试水

阿里巴巴今日宣布,将打造阿里知产开放平台,首站将试水阿
一块屏幕很难改变教育的命运,弹幕或许可以

一块屏幕很难改变教育

这成就究竟有多大程度上可以归功于远程教育的应用,是非
直击|知乎宣布架构调整 任命前蜜芽合伙人孙伟为CFO

直击|知乎宣布架构调

知乎创始人、CEO周源发布全员信,宣布组织架构调整为前
电连技术起诉乐视移动案12月17日开庭 索赔765.9万元

电连技术起诉乐视移动

电连技术公告称,公司5月委托广东格明律师事务所就与乐
最新文章
Postgresql主从异步流复制方案的深入探究

Postgresql主从异步流

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

PostgreSQL存储过程用

这篇文章主要介绍了PostgreSQL存储过程用法,结合具体
PostgreSQL中Slony-I同步复制部署教程

PostgreSQL中Slony-I

这篇文章主要给大家介绍了关于PostgreSQL中Slony-I同
Linux下创建Postgresql数据库的方法步骤

Linux下创建Postgresq

PostgreSQL 是一种非常复杂的对象-关系型数据库管理系
Windows上PostgreSQL安装配置教程

Windows上PostgreSQL

这篇文章主要为大家详细介绍了Windows上PostgreSQL安
Windows下Postgresql下载与配置方法

Windows下Postgresql

这篇文章主要为大家详细介绍了Windows下Postgresql下