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