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 thenSELECT EXTRACT(HOUR FROM current_TIME - interval '2 hours' ) into v_start_hour;
      --two hours agoSELECT EXTRACT(HOUR FROM CURRENT_TIME ) into v_end_hour;
      --get 'hour' of current timeselect 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;
    elseSELECT 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;
    EXCEPTIONWHEN 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程序设计有所帮助。

• PostgreSQL中的template0和template1库使用实战
• PostgreSQL实战之启动恢复读取checkpoint记录失败的条件详解
• postgresql影子用户实践场景分析

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！