Блог

Я работаю над преобразованием хранимой процедуры SQL Server в хранимую функцию в PostgreSQL. Я преобразовал хранимую процедуру в функцию. После преобразования я запускаю этот скрипт в pgadmin, но он выдает синтаксическую ошибку, близкую к условию if.

Сохраненная функция:

     CREATE OR REPLACE FUNCTION getuserdepartmentlist(p_nUserID  bigInt,p_sRole varchar(100),p_nInstID  bigInt = 0) RETURNS TEXT
        AS $BODY$   
            ----get dept list for non admin users
            DECLARE v_DeptID bigint;
                v_DeptIDList text;
                v_isAdmin int;
                v_isPM int;
                v_isDelgate int;
        BEGIN
         v_DeptIDList :=''; 
         v_isAdmin :=POSITION('Admin' IN p_sRole);
         v_isPM :=POSITION('PM' IN p_sRole);
         v_isDelgate :=POSITION('Delegate' IN p_sRole);

            --Cursor Declaration for Admin/pm
            DECLARE DeptCursor1 CURSOR FOR 
                        SELECT  department.ndept_id 
                                FROM   department
                                WHERE  ninst_id = p_nInstID; 

            --Cursor Declaration for non Admin/pm
            DECLARE DeptCursor2 CURSOR FOR 
                        SELECT  department.ndept_id 
                                FROM    users INNER JOIN
                                    permission ON users.nuser_id = permission.nuser_id INNER JOIN
                                    userrolemapping ON users.nuser_id = userrolemapping.nuser_id INNER JOIN
                                    department ON permission.ndept_inst = department.ninst_id
                                    AND  department.ninst_id = p_nInstID
                                WHERE     
                                    users.nuser_id = p_nUserID and
                                    spermission_type <> 'N' AND
                                    permission.sperm_level = 'D' AND 
                                    users.bis_active = 1 AND 
                                    userrolemapping.nrole_id NOT IN 
                                                (SELECT nrole_id  
                                                    FROM  roles
                                                    WHERE  srole_desc in ('Admin','PM'))                                
                    Union
                        SELECT  department.ndept_id 
                                FROM  users 
INNER JOIN permission ON users.nuser_id = permission.nuser_id 
INNER JOIN userrolemapping ON users.nuser_id = userrolemapping.nuser_id 
INNER JOIN department ON permission.ndept_inst = department.ninst_id
                                AND  department.ninst_id = p_nInstID 
                                WHERE     
                                    users.nuser_id = p_nUserID and
                                    spermission_type  <> 'N' AND
                                    permission.sperm_level = 'I' AND 
                                    users.bis_active = 1 AND 
                                    userrolemapping.nrole_id NOT IN 
                                                (SELECT nrole_id  
                                                    FROM  roles
                                                    WHERE  srole_desc in ('Admin','PM'));

            IF (v_isAdmin > 0) OR (v_isPM > 0) OR (v_isDelgate >0)
                THEN
                    --Cursor for Admin amp; PM
                    OPEN DeptCursor1;
                    FETCH NEXT FROM; DeptCursor1
                    INTO v_DeptID

                    WHILE FOUND
                        LOOP
                            v_DeptIDList := v_DeptIDList || '' || cast(v_DeptID as varchar(1)) || ','; 

                                -- Get the next vendor.
                        FETCH NEXT FROM; DeptCursor1 
                        INTO v_DeptID 
                        END LOOP; 
                    CLOSE DeptCursor1;
            ELSE    
                    --Cursor for Non Admin
                    OPEN DeptCursor2;

                    FETCH NEXT FROM; DeptCursor2 
                    INTO v_DeptID

                    WHILE FOUND
                        LOOP
                            v_DeptIDList := v_DeptIDList || '' || cast(v_DeptID as varchar(1)) || ','; 

                                -- Get the next vendor.
                        FETCH NEXT FROM; DeptCursor2 
                        INTO v_DeptID 
                        END LOOP; 
                    CLOSE DeptCursor2;

            END IF;         
                    RETURN(v_DeptIDList);
        END;
        $$LANGUAGE plpgsql;
  

Ошибка:

Он показывает синтаксическую ошибку рядом с условием if.Я добавил код ошибки ниже:

 ERROR:  syntax error at or near "IF"
LINE 61:  IF (v_isAdmin > 0) OR (v_isPM > 0) OR (v_isDelgate >0)
          ^
  

Этот код работает нормально, без ошибок.Объявление курсора неверно в приведенном выше коде.

 CREATE OR REPLACE FUNCTION getuserdepartmentlist(p_nUserID  bigInt,p_sRole varchar(100),p_nInstID  bigInt = 0)
      RETURNS TEXT
AS
$BODY$  
    ----get dept list for non admin users
    DECLARE v_DeptID bigint;
        v_DeptIDList text;
        v_isAdmin int;
        v_isPM int;
        v_isDelgate int;
        --Cursor Declaration for Admin/pm
    DECLARE DeptCursor1 CURSOR FOR 
                SELECT  department.ndept_id 
                        FROM   department
                        WHERE  ninst_id = p_nInstID; 


    --Cursor Declaration for non Admin/pm
    DECLARE DeptCursor2 CURSOR FOR 
                SELECT  department.ndept_id 
                        FROM    users INNER JOIN
                            permission ON users.nuser_id = permission.nuser_id INNER JOIN
                            userrolemapping ON users.nuser_id = userrolemapping.nuser_id INNER JOIN
                            department ON permission.ndept_inst = department.ninst_id
                            AND  department.ninst_id = p_nInstID
                        WHERE     
                            users.nuser_id = p_nUserID and
                            spermission_type <> 'N' AND
                            permission.sperm_level = 'D' AND 
                            users.bis_active = 1 AND 
                            userrolemapping.nrole_id NOT IN 
                                        (SELECT nrole_id  
                                            FROM  roles
                                            WHERE  srole_desc in ('Admin','PM'))                                
            Union
                SELECT     department.ndept_id 
                        FROM    users INNER JOIN
                                    permission ON users.nuser_id = permission.nuser_id INNER JOIN
                            userrolemapping ON users.nuser_id = userrolemapping.nuser_id INNER JOIN
                            department ON permission.ndept_inst = department.ninst_id
                            AND  department.ninst_id = p_nInstID 
                        WHERE     
                            users.nuser_id = p_nUserID and
                            spermission_type  <> 'N' AND
                            permission.sperm_level = 'I' AND 
                            users.bis_active = 1 AND 
                            userrolemapping.nrole_id NOT IN 
                                        (SELECT nrole_id  
                                            FROM  roles
                                            WHERE  srole_desc in ('Admin','PM'));



BEGIN

 v_DeptIDList :=''; 
 v_isAdmin :=POSITION('Admin' IN p_sRole);
 v_isPM :=POSITION('PM' IN p_sRole);
 v_isDelgate :=POSITION('Delegate' IN p_sRole);


    IF (v_isAdmin > 0) OR (v_isPM > 0) OR (v_isDelgate >0)
        THEN
            --Cursor for Admin amp; PM
            OPEN DeptCursor1;

            FETCH NEXT FROM DeptCursor1
            INTO v_DeptID;

            WHILE FOUND
                LOOP
                    v_DeptIDList := v_DeptIDList || '' || cast(v_DeptID as varchar(1)) || ','; 

                        -- Get the next vendor.
                FETCH NEXT FROM DeptCursor1 
                INTO v_DeptID; 
                END LOOP; 
            CLOSE DeptCursor1;
    ELSE    
            --Cursor for Non Admin
            OPEN DeptCursor2;

            FETCH NEXT FROM DeptCursor2 
            INTO v_DeptID;

            WHILE FOUND
                LOOP
                    v_DeptIDList := v_DeptIDList || '' || cast(v_DeptID as varchar(1)) || ','; 

                        -- Get the next vendor.
                FETCH NEXT FROM DeptCursor2 
                INTO v_DeptID; 
                END LOOP; 
            CLOSE DeptCursor2;

    END IF;                             
            --End cursor declaration 


            if len(v_DeptIDList)> 0 then 
                v_DeptIDList := SUBSTRING(v_DeptIDList,1,len(v_DeptIDList)-1);
            else
                v_DeptIDList :='0';
            end if;

            RETURN(v_DeptIDList);
END;
$BODY$
LANGUAGE plpgsql;