sqlserver到postgresql数据移植
sqlserver到postgresql数据移植，存储过程移植有点问题想请教大家，我贴原码
SQL存储过程原码：
CREATE PROCEDURE proQueryAD
(
@UserID INT
)
WITH ENCRYPTION
AS
BEGIN
DECLARE @ver int , @ADID int
select @ver=0
SELECT @ver=A.Ver,@ADID=A.ID from CfgAD A,CfgArea B,CfgUser C
where C.ID=@UserID and C.AreaID=B.ID and A.AreaID=B.ID
if exists (select * from ADInfo where ADID=@ADID)
begin
select @ver as 'Ver' ,Url, LinkUrl from ADInfo where ADID=@ADID
end
else
begin
SELECT @Ver as 'Ver' ,'' as 'Url' ,'' as 'LinkUrl'
end
END
GO
GRANT EXECUTE ON proQueryAD TO PUBLIC
GO
我个人转换的PG存储过程原码：
CREATE OR REPLACE FUNCTION proQueryAD(U_UserID INTEGER) RETURNS SETOF RECORD AS
$$
DECLARE
v_Ver INTEGER;
v_ADID INTEGER;
v_rec RECORD;
BEGIN
v_Ver := 0;
v_ADID := 0;
FOR v_rec IN SELECT A.Ver, A.ID from CfgAD A,CfgArea B,CfgUser C
where C.ID=U_UserID and C.AreaID=B.ID and A.AreaID=B.ID LOOP
v_Ver=v_rec.Ver;
v_ADID=v_rec.ID;
RETURN NEXT v_rec;
END LOOP;
BEGIN
IF EXISTS(select * from ADInfo where ADID=v_ADID) THEN
BEGIN
select v_Ver as Ver ,Url, LinkUrl from ADInfo where ADID=V_ADID;
END;
ELSE
BEGIN
SELECT v_Ver as Ver ,'' as Url ,'' as LinkUrl;
END;
END;
RETURN;
END;
$$
LANGUAGE PLPGSQL;
我想返回最下面的判断语句中查询到的内容，返回值要从新写，该如何写？或者大家有没有更好的转换代码贴上给我参考一下！
[解决办法]
你返回的记录集是三个字段, 为integer, text, text
而你声明的是t(Ver INTEGER, ID INTEGER), 不匹配啊
