PostgreSQL 存储过程函数改写

[Brandon_Lee]

CREATE PROCEDURE [dbo].[GETSID]
(
@table varchar(20),
@letter varchar(20),
@pkey varchar(20),
@newid nvarchar(14) output
)
AS
BEGIN 
DECLARE @DATE DATETIME
DECLARE @YYYY VARCHAR(4)
DECLARE @MM VARCHAR(2)
DECLARE @DD VARCHAR(2)
DECLARE @sql NVARCHAR(200)
DECLARE @oldid VARCHAR(14)
--获取当前日期
SET @DATE=GETDATE()
SET @YYYY=DATEPART(YYYY,@DATE)
SET @MM=DATEPART(MM,@DATE)
SET @DD=DATEPART(DD,@DATE)
--位数不够补零
SET @YYYY=REPLICATE('0',4-LEN(@YYYY))+@YYYY
SET @MM=REPLICATE('0',2-LEN(@MM))+@MM
SET @DD=REPLICATE('0',2-LEN(@DD))+@DD
--获取当前日期的最大编号值
set @sql='select top 1 @oldid=['+@pkey+'] from '+@table+' where '+@pkey+
' like '''+@letter+@YYYY+@MM+@DD+'%'' Order by CONVERT(int,RIGHT('+@pkey+',4)) desc'
EXEC SYS.sp_executesql @sql,N'@oldid nvarchar(16) OUT',@oldid OUT
--select top 1 @oldid=[id] from test where [id] like @letter+@YYYY+@MM+@DD+'%' order by convert(int,right(id,4)) desc 
--如果@oldid为空
IF @oldid IS NULL
    set @newid=(@letter+@YYYY+@MM+@DD+'0001')
ELSE
BEGIN
    DECLARE @NUM VARCHAR(4)
    SET @NUM=CONVERT(VARCHAR, (CONVERT(INT, RIGHT(@oldid,4))+ 1))
    SET @NUM=REPLICATE('0',4-LEN(@NUM))+@NUM
    --生成编号
    SET @newid=@letter+@YYYY+@MM+@DD+@NUM
    END
END

将以上SQL Server 存储过程改写为postgreSQL的函数

[WWWWA]

自己动手做一下吧，具体问题再咨询

[Brandon_Lee]

-- Function: produceid(character varying, character varying, character varying) -- DROP FUNCTION produceid(character varying, character varying, character varying); CREATE OR REPLACE FUNCTION produceid1(tablename character varying, columnname character varying, letter character varying) RETURNS character varying AS $BODY$ declare --声明所有要使用的变量 danow timestamp=current_timestamp; yyyy varchar(4):=extract(year from danow);--年份等的赋值 mm varchar(2):=extract(month from danow); dd varchar(2):=extract(day from danow); tlike varchar(16);--like查询条件组合 newid varchar(14);--产生的新ID sql varchar(200); num int; begin --日期不够格式的补零 yyyy:=lpad(yyyy,4,'0'); mm:=lpad(mm,2,'0'); dd:=lpad(dd,2,'0'); tlike:=letter||yyyy||mm||dd; --sql:='select top1 newid=['||columnname||' from'||tablename||' where '||columnname||' like '||tlike||''%' order by cast(substring('||columnname||',10,4) as integer) desc'; --cast(substring('||columnname||',10,4) as integer) --sql:=select newid=columnname from tablename where columnname like tlike||'%' and cast(substring(columnname,10,4))=Max(cast(substring(coluname,10,4)as integer)) --sql:='select newid='||columnname||' from '||tablename||' where '||columnname||' like '||tlike||'% and cast(substring('||columnname||',10,4) as integer)=max(cast(substring('||columnname||',10,4)as integer))'; --sql:='select '||quote_literal(newid)||'='||quote_ident(columnname)||' from '||quote_ident(tablename)||' where '||quote_ident(columnname)||' like '||quote_literal(tlike)||'% and cast(substring('||quote_ident(columnname)||',10,4) as integer)=max(cast(substring('||quote_ident(columnname)||',10,4) as integer))'; execute 'select ' ||quote_literal(newid) ||'=' ||quote_ident(columnname) ||' from ' ||quote_ident(tablename) ||' where ' ||quote_ident(columnname) ||' like ' ||quote_literal(tlike) ||'% and cast(substring(' ||quote_ident(columnname) ||',10,4) as integer)=max(cast(substring(' ||quote_ident(columnname) ||',10,4) as integer))'; --execute(sql); if newid is null or newid='' then newid:=tlike+'00001'; else num:=cast(substring(newid,10,4) as integer); num:=num+1; newid:=cast(num as varchar) ; newid:=tlike||newid; end if; return newid; end; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION produceid(character varying, character varying, character varying) OWNER TO postgres; 这是我写的function 调试的时候总是在execute部分报错

[trainee]

里面有很多错误 1、MSSQL中的这种语法 select top 1 @newid=yyy 在postgresql 是行不通的 应该是 execute 'select yyy ... limit 1' into newid 2、newid:=tlike+'00001'; 应该是 newid:=tlike||'00001';

[Jerry_Li-CN]

引用 2 楼 u012746728 的回复:

-- Function: produceid(character varying, character varying, character varying) -- DROP FUNCTION produceid(character varying, character varying, character varying); CREATE OR REPLACE FUNCTION produceid1(tablename character varying, columnname character varying, letter character varying) RETURNS character varying AS $BODY$ declare --声明所有要使用的变量 danow timestamp=current_timestamp; yyyy varchar(4):=extract(year from danow);--年份等的赋值 mm varchar(2):=extract(month from danow); dd varchar(2):=extract(day from danow); tlike varchar(16);--like查询条件组合 newid varchar(14);--产生的新ID sql varchar(200); num int; begin --日期不够格式的补零 yyyy:=lpad(yyyy,4,'0'); mm:=lpad(mm,2,'0'); dd:=lpad(dd,2,'0'); tlike:=letter||yyyy||mm||dd; --sql:='select top1 newid=['||columnname||' from'||tablename||' where '||columnname||' like '||tlike||''%' order by cast(substring('||columnname||',10,4) as integer) desc'; --cast(substring('||columnname||',10,4) as integer) --sql:=select newid=columnname from tablename where columnname like tlike||'%' and cast(substring(columnname,10,4))=Max(cast(substring(coluname,10,4)as integer)) --sql:='select newid='||columnname||' from '||tablename||' where '||columnname||' like '||tlike||'% and cast(substring('||columnname||',10,4) as integer)=max(cast(substring('||columnname||',10,4)as integer))'; --sql:='select '||quote_literal(newid)||'='||quote_ident(columnname)||' from '||quote_ident(tablename)||' where '||quote_ident(columnname)||' like '||quote_literal(tlike)||'% and cast(substring('||quote_ident(columnname)||',10,4) as integer)=max(cast(substring('||quote_ident(columnname)||',10,4) as integer))'; execute 'select ' ||quote_literal(newid) ||'=' ||quote_ident(columnname) ||' from ' ||quote_ident(tablename) ||' where ' ||quote_ident(columnname) ||' like ' ||quote_literal(tlike) ||'% and cast(substring(' ||quote_ident(columnname) ||',10,4) as integer)=max(cast(substring(' ||quote_ident(columnname) ||',10,4) as integer))'; --execute(sql); if newid is null or newid='' then newid:=tlike+'00001'; else num:=cast(substring(newid,10,4) as integer); num:=num+1; newid:=cast(num as varchar) ; newid:=tlike||newid; end if; return newid; end; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION produceid(character varying, character varying, character varying) OWNER TO postgres; 这是我写的function 调试的时候总是在execute部分报错

execute是想赋值吗？可以用SELECT 字段 INTO 变量来赋值