PostgreSQL存儲(chǔ)過程用法實(shí)戰(zhàn)詳解
本文實(shí)例講述了postgreSQL存儲(chǔ)過程用法。分享給大家供大家參考,具體如下:
轉(zhuǎn)了N多的SQL語句,可是自己用時(shí),卻到處是坑啊,啊,啊!!!!!!!!!!!!!!!
想寫一個(gè)獲取表中最新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);
下面要寫個(gè)存儲(chǔ)過程,以獲取表中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; --大家可以試一下,上面這個(gè)是會(huì)報(bào)錯(cuò)的 --select f_getNewID('department','ID'); --出錯(cuò)!
看了官方文檔,人家就是這么用的:
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date;
你確定你看清楚了?????
確定你讀完讀懂了說明書?????
--這個(gè)看了? --------------------------------------- EXECUTE 'SELECT count(*) FROM ' || quote_ident(tabname) || ' WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date;
--這個(gè)看了? --------------------------------------- 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? --注意 對(duì)象(表名、字段名等)是不可以直接用變量的,要用 quote_ident() ------------------------------------------------------- postgres=# select f_getnewid('department','ID'); --錯(cuò)誤: 字段 "ID" 不存在 --第1行select max("ID") from department ^ --查詢: select max("ID") from department --背景: 在EXECUTE的第10行的PL/pgSQL函數(shù)f_getnewid(text,text) --=============================== --什么情況,ID怎么會(huì)有雙引號(hào),引號(hào),號(hào),號(hào)??? ---------------------------------------------------------- --這里要感謝大神:權(quán)宗亮@飛象數(shù)據(jù) --改成這樣: postgres=# select f_getnewid('department','id'); f_getnewid ------------ 2 (1 行記錄) ----終于成功了!大小寫還有區(qū)別嗎??? --but why? --當(dāng)在命令行輸入 CREATE TABLE role( ID INT PRIMARY KEY NOT NULL, r_name VARCHAR(50) NOT NULL, r_paretnID INT NOT NULL DEFAULT 0 ); --結(jié)果在pgAdmin里看到的卻是小寫的
--同樣,如果是在QUERY TOOLS 下用這樣的語句創(chuàng)建還是 所有的字體名為小寫 --如果我就想大寫怎么辦???? --要這樣寫 CREATE TABLE "RoleUPER"( "ID" INT PRIMARY KEY NOT NULL, r_name VARCHAR(50) NOT NULL, "r_paretnID" INT NOT NULL DEFAULT 0 ); --再用大象看看
可以了!
總結(jié)一下:
1、存儲(chǔ)過程(FUNCITON)變量可以直接用 ||
拼接。上面沒有列出,下面給個(gè)栗子:
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、存儲(chǔ)過程的對(duì)象不可以直接用變量,要用 quote_ident(objVar)
3、$1 $2是 FUNCTION 參數(shù)的順序,如1中的 $1 $2交換,USING 后面的不換 結(jié)果 :
select max(myTableName) from myFeildname
4、注意:SQL語句中的大寫全部會(huì)變成小寫,要想大寫存大,必須要用雙引號(hào)。
附:一個(gè)完整postgreSQL 存儲(chǔ)過程示例
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;
希望本文所述對(duì)大家PostgreSQL程序設(shè)計(jì)有所幫助。
相關(guān)文章
PostgreSQL 實(shí)現(xiàn)sql放入文件批量執(zhí)行
這篇文章主要介紹了PostgreSQL 實(shí)現(xiàn)sql放入文件批量執(zhí)行,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-02-02postgreSQL數(shù)據(jù)庫的監(jiān)控及數(shù)據(jù)維護(hù)操作
這篇文章主要介紹了postgreSQL數(shù)據(jù)庫的監(jiān)控及數(shù)據(jù)維護(hù)操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01PostgreSQL?流復(fù)制配置環(huán)境搭建過程
PostgreSQL 流復(fù)制是 9.0 提供的一種新的 WAL 傳遞方法,使用流復(fù)制時(shí),每當(dāng) Primary 節(jié)點(diǎn) WAL 產(chǎn)生,就會(huì)馬上傳遞到 Standby 節(jié)點(diǎn),流復(fù)制提供異步和同步兩種模式,同步模式可以保障數(shù)據(jù) 0 丟失,這篇文章主要介紹了PostgreSQL?流復(fù)制搭建,需要的朋友可以參考下2023-09-09CentOS PostgreSQL 12 主從復(fù)制(主從切換)操作
這篇文章主要介紹了CentOS PostgreSQL 12 主從復(fù)制(主從切換)操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01PostgreSQL導(dǎo)出數(shù)據(jù)庫表(或序列)的結(jié)構(gòu)和數(shù)據(jù)實(shí)例代碼
這篇文章主要給大家介紹了關(guān)于PostgreSQL導(dǎo)出數(shù)據(jù)庫表(或序列)的結(jié)構(gòu)和數(shù)據(jù)的相關(guān)資料,你可以使用pg_dump命令來導(dǎo)出PostgreSQL數(shù)據(jù)庫中的表結(jié)構(gòu)和數(shù)據(jù),需要的朋友可以參考下2023-10-10postgresql 計(jì)算時(shí)間差的秒數(shù)、天數(shù)實(shí)例
這篇文章主要介紹了postgresql 計(jì)算時(shí)間差的秒數(shù)、天數(shù)實(shí)例,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2020-12-12postgres 使用存儲(chǔ)過程批量插入數(shù)據(jù)的操作
這篇文章主要介紹了postgres 使用存儲(chǔ)過程批量插入數(shù)據(jù)的操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-02-02