mohitnarang01 0 Newbie Poster

Hi,

I am in urgent need for converting sql procedure to postgre sql procedure.Please help me ASAP.

CREATE FUNCTION sp_get_id_des (IN cellid INT, IN mcc VARCHAR(3), IN mnc VARCHAR(3), IN nwtype INT, IN mlac INT , IN mrac INT ) returns SETOF record AS'

 declare pscnt int ;
 declare cscnt int;
 declare cnt1 int ;
 declare cnt2 int;
 declare cscnid int ;
 declare pscni int;
 declare cscnpoolid varchar(20);
 declare pscnpoolid varchar(20);
 **DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @x2 = 1;**
BEGIN
 drop table TEMP_CNINFO_TBL;
 drop table RESULT_TBL;
 create temporary table TEMP_CNINFO_TBL select * from FGW_DBS_CNINFO_TBL where CNINFO_MLAC=mlac and CNINFO_MRAC=mrac and CNINFO_MCC=mcc and CNINFO_MNC=mnc and CNINFO_CN_STATUS=1 and CNINFO_CN_IUSTATE<>2;
 select count(*) into pscnt from TEMP_CNINFO_TBL where CNINFO_MLAC=mlac and CNINFO_MRAC=mrac and CNINFO_MCC=mcc and CNINFO_MNC=mnc and CNINFO_CN_TYPE=2;
 select count(*) into cscnt from TEMP_CNINFO_TBL where CNINFO_MLAC=mlac and CNINFO_MRAC=mrac and CNINFO_MCC=mcc and CNINFO_MNC=mnc and CNINFO_CN_TYPE=1;
 if(pscnt <> 0 && cscnt <> 0) then
  select count(*) into cnt1 from FGW_DBS_FCODES_TBL where FCODES_CELL_ID=cellid and FCODES_MCC=mcc and FCODES_MNC=mnc and FCODES_NW_TYPE=nwtype and FCODES_MLAC=mlac and FCODES_MRAC=mrac;
  if(cnt1 <> 0) then
   create temporary table RESULT_TBL select FCODES_ID, FCODES_IU_LAC,FCODES_UU_LACLIST,FCODES_SAC,FCODES_RAC,FCODES_MCC,FCODES_MNC from FGW_DBS_FCODES_TBL where FCODES_CELL_ID=cellid and FCODES_MCC=mcc and FCODES_MNC=mnc and FCODES_NW_TYPE=nwtype and FCODES_MLAC=mlac and FCODES_MRAC=mrac;
   select CNINFO_CN_ID into cscnid from TEMP_CNINFO_TBL where CNINFO_CN_TYPE=1 ORDER BY RAND() LIMIT 1;
   select CNINFO_CN_ID into pscnid from TEMP_CNINFO_TBL where CNINFO_CN_TYPE=2 ORDER BY RAND() LIMIT 1;
   select GROUP_CONCAT(DISTINCT CNINFO_CNPOOL_ID) into cscnpoolid from TEMP_CNINFO_TBL where CNINFO_CN_TYPE=1 and CNINFO_CNPOOL_ID<>0;
   select GROUP_CONCAT(DISTINCT CNINFO_CNPOOL_ID) into pscnpoolid from TEMP_CNINFO_TBL where CNINFO_CN_TYPE=2 and CNINFO_CNPOOL_ID<>0;
   if(cscnpoolid is NULL) then
    set cscnpoolid=0;
   end if;
   if(pscnpoolid is NULL) then
    set pscnpoolid=0;
   end if;
   select cscnid,pscnid,cscnpoolid,pscnpoolid,FCODES_ID,FCODES_IU_LAC,FCODES_UU_LACLIST,FCODES_SAC,FCODES_RAC,FCODES_MCC,FCODES_MNC from RESULT_TBL;
  else
   select count(*) into cnt2 from FGW_DBS_FCODES_TBL where FCODES_CELL_ID=-1 and FCODES_MCC=mcc and FCODES_MNC=mnc and FCODES_NW_TYPE=nwtype and FCODES_MLAC=mlac and FCODES_MRAC=mrac;
   if(cnt2 <> 0) then
    create **temporary table** RESULT_TBL select FCODES_ID,FCODES_IU_LAC,FCODES_UU_LACLIST,FCODES_SAC,FCODES_RAC,FCODES_MCC,FCODES_MNC from FGW_DBS_FCODES_TBL where FCODES_CELL_ID=-1 and FCODES_MCC=mcc and FCODES_MNC=mnc and FCODES_NW_TYPE=nwtype and FCODES_MLAC=mlac and FCODES_MRAC=mrac;
    select CNINFO_CN_ID into cscnid from TEMP_CNINFO_TBL where CNINFO_CN_TYPE=1 ORDER BY RAND() LIMIT 1;
    select CNINFO_CN_ID into pscnid from TEMP_CNINFO_TBL where CNINFO_CN_TYPE=2 ORDER BY RAND() LIMIT 1;
    select **GROUP_CONCAT**(DISTINCT CNINFO_CNPOOL_ID) into cscnpoolid from TEMP_CNINFO_TBL where CNINFO_CN_TYPE=1 and CNINFO_CNPOOL_ID<>0;
    select GROUP_CONCAT(DISTINCT CNINFO_CNPOOL_ID) into pscnpoolid from TEMP_CNINFO_TBL where CNINFO_CN_TYPE=2 and CNINFO_CNPOOL_ID<>0;
    if(cscnpoolid is NULL) then
     set cscnpoolid=0;
    end if;
    if(pscnpoolid is NULL) then
     set pscnpoolid=0;
    end if;
    select cscnid,pscnid,cscnpoolid,pscnpoolid,FCODES_ID,FCODES_IU_LAC,FCODES_UU_LACLIST,FCODES_SAC,FCODES_RAC,FCODES_MCC,FCODES_MNC from RESULT_TBL;
   end if;
  end if;
 end if;
END;'
assert "Create sp_get_id_des"

Please help me in converting above procedure into postgre
Please suggest alternative in postgresql for the given sql statement DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @x2 = 1;, GROUP_CONCAT,temporary table.

Thanks and regards
Mohit

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.