POSTGRESQL 預存程序實戰

標籤：沒有   查詢   har   img   大神   建立   記錄   pre   一個   

轉了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 預存程序實戰