1、后台生成XML作为参数然后数据库解析获取数据
var idList = ids.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); var root = new XElement("xml"); if (idList.Length > 0) { foreach (var id in idList) { var rightX = new XElement("item"); rightX.SetAttributeValue("id", id); root.Add(rightX); } } string projectXml = root.ToString().Replace("<xml>", "<xml xmlns=\"xmlns\">");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