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函數(shù)f_getnewid(text,text)--===============================--什么情況，ID怎么會有雙引號，引號，號，號？？？------------------------------------------------------------這里要感謝大神：權宗亮@飛象數(shù)據(jù)--改成這樣：postgres=# select f_getnewid('department','id'); f_getnewid------------     2(1 行記錄)----終于成功了！大小寫還有區(qū)別嗎？？？ --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 下用這樣的語句創(chuàng)建還是 所有的字體名為小寫--如果我就想大寫怎么辦？？？？--要這樣寫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 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;