PostgresSQL 知识点记录

pgsql中的pl/pgsql 使用不是很方便，后面加了xmlns属性是用来转换表格获取节点，官方文档是这样的。。

CREATE OR REPLACE FUNCTION public.fn_SchoolBotProjectSet(Schoolid TEXT,projectxml TEXT)
 RETURNS numeric
 LANGUAGE plpgsql
AS $function$
DECLARE
	_schoolid integer := schoolid;
	_projectxml XML := projectxml;
	r_count integer :=0;   
BEGIN
	IF _projectxml IS DOCUMENT THEN
--		-- 解析xml保存入表变量：项目
		CREATE TEMPORARY TABLE Project(
			Id CHAR(12) 
		);
	
		WITH xmldata(data) AS (VALUES (_projectxml::xml))
		
		INSERT INTO Project 
		SELECT xmltable.*
	  	FROM XMLTABLE(XMLNAMESPACES(‘xmlns‘ AS x),
	  				‘/x:xml/x:item‘
	                PASSING (SELECT data FROM xmldata)
	                COLUMNS id text PATH ‘@id‘);  
	               
	    IF NOT EXISTS (SELECT 1 FROM md_UserBotProject AS pro WHERE pro.SchoolId=_schoolid) THEN       
			INSERT INTO md_UserBotProject
			SELECT Project.Id, _schoolid,NULL
			FROM Project
			LEFT OUTER JOIN md_UserBotProject ON (md_UserBotProject.ProjectId = Project.Id);
	--		WHERE md_UserBotProject.ProjectId IS NULL;
			GET DIAGNOSTICS r_count := row_count; 				
		ELSE
			DELETE FROM md_UserBotProject AS pro WHERE pro.schoolid=_schoolid;
			INSERT INTO md_UserBotProject
			SELECT Project.Id, _schoolid,NULL
			FROM Project
			LEFT OUTER JOIN md_UserBotProject ON (md_UserBotProject.ProjectId = Project.Id);
			GET DIAGNOSTICS r_count := row_count;  --获取操作行数
		END IF;
	--		DROP TABLE Project; --最后需要DROP临时表
		IF r_count >0 THEN
			RETURN 1;
		ELSE 
			RETURN 0;  
		END IF;
	ELSE
		RETURN -1;
	END IF;    
END;
$function$
;

XML参数格式

<xml xmlns="xmlns">
<item id="5f303b8c0001" />
<item id="5f303b980002" />
</xml>

2、操作数据之后返回自动增长列的值（增删改都可以，houseid为自动增长列）

DELETE FROM md_SchoolHouse WHERE houseid = 2 RETURNING houseid;
--md_bathmachine表名   machineid列名
_MachineId := (SELECT currval(‘md_bathmachine_machineid_seq‘::regclass) AS id);

3、清空数据并初始化自动增长列1（student为表名）

--清空表数据
TRUNCATE student;
--清空表数据，自增从1开始
TRUNCATE student RESTART IDENTITY;

4、函数中获取操作行数，类似SQLServer中几行受影响

r_count integer :=0;   
GET DIAGNOSTICS r_count := row_count; 

5、函数中返回结果集（返回数据列要对应）

--返回类型改为实体表
RETURNS SETOF md_users
--返回查询结果
RETURN QUERY SELECT * FROM md_Users u WHERE u.userid = _userid;

返回类型也可以是自定义表（返回数据列要对应）

RETURNS TABLE(moneytotal numeric, counttotal bigint, persontotal bigint)

6、SQL拼接

SqlStr text;--声明变量
_Condition text;--声明变量
SqlStr:=‘SELECT aa ‘;
SqlStr:=SqlStr||‘,0.00 as CardTotal,0.00 as AppTotal FROM public.md_CashDeposit WHERE ‘ || _Condition  || ‘;‘;
return QUERY execute SqlStr;--返回类型为结果集时

7、关联更新数据

update public.md_BathMachine as bm set UBotId=rb.UBotId from public.md_SchoolRoomFloorBind as rb 
where bm.RoomFloorId=rb.RoomFloorId and rb.ProjectType=_ProjectType;

8、时间相关处理

SELECT NOW(); --2020-08-11 20:03:50
SELECT CURRENT_TIMESTAMP;--2020-08-11 20:03:59
SELECT CURRENT_TIME ;--20:04:10
SELECT CURRENT_DATE;--2020-08-11
SELECT NOW() + INTERVAL ‘10 year‘;--2030-08-11 20:05:52  min/year/month/day/hour/sec/

9、查询是如果为NULL时重新赋值，类似MSSQL中ISNULL

SELECT COALESCE(NULL,‘123‘) != ‘13‘;

10、判断否个字段中是否包含否字符串，类似CHARINDEX(@Param,ColumnName)

SELECT distinct FloorNo,HouseId FROM public.v_SchoolRoomFloor WHERE POSITION(‘13000205F100001‘ IN UInstallId) >0 AND HouseId=1 AND Status=1;
SELECT distinct FloorNo,HouseId FROM public.v_SchoolRoomFloor WHERE STRPOS(UInstallId,‘13000205F100001‘) >0 AND HouseId=1 AND Status=1;
--可查询多个
SELECT distinct FloorNo,HouseId FROM public.v_SchoolRoomFloor WHERE (STRING_TO_ARRAY(‘13000205F100001,1211212‘, ‘,‘) && STRING_TO_ARRAY(UInstallId, ‘,‘))

11、修改默认值

ALTER TABLE public.md_bathnbonoffvalve ALTER COLUMN status SET DEFAULT ‘1‘::integer;

12、生成分页SQL语句

public static string GetNpgSqlPagingSql(PageCriteria criteria)
    {
        var sbSql = new StringBuilder();
        sbSql.AppendFormat("select * from ( select row_number() over(order by {0}) as rowid,* from {1}) as subt \n", criteria.Sort, criteria.TableName);
        sbSql.AppendFormat("where subt.rowid>=({0}-1)*{1}+1 and subt.rowid<={0}*{1};\n", criteria.CurrentPage, criteria.PageSize);
        return sbSql.ToString();
    }

    /// <summary>
    /// 封装查询条件相关信息的类
    /// </summary>
    [Serializable]
    public class PageCriteria
    {
        public string TableName { get; set; }
        public string PrimaryKey { get; set; }
        public int PageSize { get; set; }
        public int CurrentPage { get; set; }
        public string Sort { get; set; }
        public string Condition { get; set; }
        /// <summary>
        /// 总行数
        /// </summary>
        public int RecordCount { get; set; }
    }

13、获取时间部分

SELECT DATE_PART(‘hour‘,NOW());
SELECT DATE_PART(‘minute‘,NOW());
SELECT DATE_PART(‘second‘,NOW());

14、类型转换

SELECT cast(‘1234.33‘ as NUMERIC(18,2));
SELECT to_number(‘12121231231.8‘, ‘99999999999.99‘);
SELECT to_char(1234566.35, ‘99999999999‘);
SELECT to_number(‘1212.8‘, ‘99G999D9S‘);
SELECT to_number(‘12121231231.8‘, ‘99999999999.99‘);
SELECT replace(‘123456789‘, ‘456‘, ‘000‘);

15、数据库备份还原

psql -h localhost -U postgres -d databasename <  C:\databasename.bak
指令解释：如上命令，psql是恢复数据库命令，localhost是要恢复到哪个数据库的地址，当然你可以写上ip地址，也就是说能远程恢复（必须保证 数据库允许外部访问的权限哦~）；postgres 就是要恢复到哪个数据库的用户；databasename 是要恢复到哪个数据库。<  的意思是把C:\databasename.bak文件导入到指定的数据库里。

在linux里依然有效。有一个值得注意的是：如果直接进入PostgreSQL的安装目录bin下，执行命令，可能会出现 找不到pg_dump，psql的现象，我们在可以这样：
备份：
/opt/PostgreSQL/9.5/bin/pg_dump -h 164.82.233.54 -U postgres databasename > databasename.bak
恢复： 
/opt/PostgreSQL/9.5/bin/psql -h localhost -U postgres -d databasename < databasename.bak

PostgresSQL 知识点记录

标签：处理   summary   初始化   stringbu   val   数据   replace   otp   sort