sql转psql

1.日期转换

CONVERT(VARCHAR(10),GetDate(),23))

to_char(now(),yyyy-MM-dd)

extract(year from now());

2.日期加减

表示三天后

select now()   interval 3 day;

select to_char(now()   interval 7 day,yyyy-MM-dd)

每月最后一天

select  (to_char(now()  interval 1 month ,yyyy-MM-) || 01 ) :: date - interval 1 day

3.使用游标

open v_result2 for SELECT * FROM tempJAN;

return next v_result2;

4.isNULL –> COALESCE

5.@ –> v_

6.[]

7.len –> length

8.删除临时表

if  exists( select * from information_schema.tables  where table_schema like pg_temp%  and table_name = lower(TempCompanyMsg) )

then

    drop table TempCompanyMsg;

end if;

CREATE temp table TempScore AS

9.dbo –> management

10.CHARINDEX –> POSITION(/-/ in v_ORDER_NUM_STR);

11.获取日期

DATEDIFF

extract(month from now());

12.查看游标内容

BEGIN ;

select * from mdifdb.proc_md_BranchProductInfoReferWeb(4,4901331009,%%,%%,%%,,,,,,,,,,,,);

fetch all in "<unnamed portal 1>"

END ;

13.DATEDIFF

date_part(day, 2015-01-15 17:05::timestamp - 2013-01-14 16:05::timestamp);

14.Round((KCountItem*1.0/SumCountItem),2,1)

trunc((KCountItem*1.0/SumCountItem),2)

15.返回结果集

FOR v_rec IN

    SELECT

        id, branchcd, productcd, productname_read, brandname, specname_read,

        makerprodcd, departmentcd, colorname, sizename, innercaseqty,

            caseqty, orderunit, deliveryqty, costprice, salesprice, costsum,

            salessum, suppliercd, suppliername, deliverydate, empcd, empname,

            commitflag, author, registered, maintainer, modified,istransfer,slipno,slipprocessno

    FROM delivery.deliveryinfo_transfer

    WHERE istransfer = 0  OR istransfer IS NOT NULL

LOOP

RETURN NEXT v_rec;

END LOOP;

16.截取ip前3段

split_part(v_network_ip,'.',1)

substring(v_serverip from '[0-9] .[0-9] .[0-9] ')

来源张永光的博客