PgSql 主键自增长、函数demo

主键自增长

创建主键自增长
table :表名

CREATE SEQUENCE table_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
alter table "table" alter column id set default nextval('table_id_seq ');
SELECT setval('table_id_seq',max(id)) from "table";

函数(表格返回值)

CREATE OR REPLACE FUNCTION "public"."proc_gettotal_suspend"()
  RETURNS TABLE("省份名称" varchar, "城市名称" varchar, "区县名称" varchar, "编码" varchar, "名称" varchar, "控制级别" varchar, "流域名称" varchar, "所属河流" varchar) AS $BODY$BEGIN
	-- Routine body goes here...


  RETURN QUERY SELECT
	( SELECT  name FROM "public"."Base_AdministrativeDivision" WHERE Code = ProvinceCode limit 1) 省份,
	CityName 城市名称,
	DistrictName 区县名称,
	StationCode 编码,
	StationName 名称,
	( SELECT  ValueText FROM "public"."Base_FieldValue" WHERE FieldKey = 'Hierarchy' AND ValueCode = HierarchyCode limit 1) 控制级别,
	WatershedName 流域名称,
	RiverName 所属河流 
FROM
	"public"."Business_Station" 
WHERE
	is_deleted = 0 
	AND IsRevoke = 0 
	AND IsUse = 1 
	AND stationcode IN ( SELECT StationCode FROM "public"."Business_StationChange" WHERE IsType = 3 AND AppStatus = 2 AND startTime <= now( ) AND now( ) <= EndTime ) 
ORDER BY
	ProvinceCode;
	 

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1e+08

使用函数：select * from proc_gettotal_suspend()

函数(游标使用demo)

CREATE OR REPLACE FUNCTION "public"."proc_insertstationitem"()
  RETURNS "pg_catalog"."int8" AS $BODY$  
declare  
    cur_films CURSOR 
       FOR SELECT "id" FROM "Business_Station" Where id not in (select "Business_StationItem"."StationId" FROM "Business_StationItem" );
    v_id int8;  
		v_count int8;
begin  
		v_count:=0;
    open cur_films ;  
    loop  
				-- fetch row into the film
				FETCH cur_films INTO v_id;
				-- exit when no more row to fetch
				EXIT WHEN NOT FOUND;

				--数据处理开始
				--数据处理结束
				
				v_count:=v_count+1;
    end loop;  
    close cur_films;  
		return v_count;
end;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

select cursor_demo311new3();