MSSQL,MYSQL和POSTGRES数据库的一些区别

一、字符串连接

1、MSSQL

SELECT 字段1 + '-' + 字段2 FROM TABLE；

2、MYSQL

SELECT CONCAT(字段1,'-',字段2) FROM TABLE;

3、POSTGRES

SELECT 字段1||'-'||字段2 FROM TABLE；

二、SELECT INTO

1、MSSQL和MYSQL

SELECT 字段1... INTO 变量1... FROM

2、POSTGRES

SELECT INTO 变量1 字段1 FROM

如：SELECT INTO strIds ('MANAGER_'||repeat(str0,5 - iLength)||strId);

三、判断某字段在传递的以逗号分开的字符串中

1、MSSQL

SELECT  Kname FROM  Kind WHERE  CHARINDEX(','+RTRIM(LTRIM(Kid))+',',','+@ids+',')>0

2、POSTGRES

SELECT  Kname FROM  Kind WHERE POSITION(','||"Kid"||',' IN ','||ids||',') > 0 ;

四、获取UTC时间

1、POSTGRES

SELECT INTO strSysTime localtimestamp(0);
SELECT INTO sq date_part('timezone_hour',current_timestamp)||'hours';
SELECT INTO strSysTime (strSysTime - sq );

五、变量声明

1、MSSQL和MYSQL

DECLARE 在BEGIN END之中

2、POSTGRES

DECLARE 在BEGIN之前，且只能书写一次

如：

CREATE OR REPLACE FUNCTION "funUpdateGlobalInfoTableConfigureTS"()
RETURNS TRIGGER AS
$$
DECLARE
strSysTime TIMESTAMP;
sq interval;
BEGIN
SELECT INTO strSysTime localtimestamp(0);
SELECT INTO sq date_part('timezone_hour',current_timestamp)||'hours';
SELECT INTO strSysTime (strSysTime - sq );
UPDATE "GlobalInfoTable"
SET
 "ConfigureTS" = strSysTime;
RETURN NULL;
END;
$$LANGUAGE plpgSQL;

六、其他

1、POSTGRES

可以直接返回游标形式，如；

CREATE OR REPLACE FUNCTION "funSelectInfoFromFTPServerTable"()
RETURNS refcursor AS
$$
DECLARE recordcur refcursor;
BEGIN
OPEN recordcur FOR
SELECT
 "host",
 "port",
 "account",
 "pwd"
FROM
 "FTPServerTable"
ORDER BY "index";
RETURN recordcur;
END;
$$
LANGUAGE PLpgSQL;

数字转换为字符串及删除空格

trim(leading ' ' from ((to_char(0,'9999999999'))))