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