PostgreSQL存储过程用法实战详解

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);

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

);

--再用大象看看

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;

select max (myTableName) from myFeildname

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;