Question 284665 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Else error appears in function in postgressql

I have this function in postgressql, the p_flg_init set the if branch :

CREATE OR REPLACE FUNCTION dwh.f_dwh_post_dm_updt_vend_cust_serv(p_flg_init varchar DEFAULT 'N')
 RETURNS text
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE

   proc_step       varchar := NULL; 
   nTemId     numeric := NULL;
   qta_nobilia numeric := NULL;
    vcErrore                            varchar(4000);
      vcStato                             varchar(10); -- (N)ormale / (W)arning / (E)rrore / (NA) Non Eseguito
      idProcedura                         numeric := 0;
      codProcedura                        varchar(50) := 'POST_DM_UPDT_VEND_CUST_SERV';

    v_state   TEXT;
    v_msg     TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT; 

 ANNOMESE RECORD;

begin
    

IF(p_flg_init='Y')
then
begin
   
   proc_step := 'LOOP ANNOMESE';
   BEGIN
   FOR ANNOMESE 
    IN (
         SELECT
               mf_id  FROM d_tempo WHERE mf_id
               BETWEEN To_Char(current_date-1+interval '-24 month','YYYYMM')::integer and  To_Char(current_date-1+interval '-1 month','YYYYMM')::integer              
           
  -- intervallo rolling 12 mese chiuso

       )
   LOOP
      statement in loop         ;
   END LOOP;  
end;
else
begin
statement that not use lopp    ;
end;
end if; 


  
  
  
  
END;
$function$
;

when I compile the code the error : SQL Error [42601]: ERROR: syntax error at or near "else"
Position: 5669 appears.
If I write the function without using else it works fine

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I've removed begin and end and now I have different error in exception : ERROR: syntax error at or near "exception" LINE 281: exception

    CREATE OR REPLACE FUNCTION dwh.f_dwh_post_dm_updt_vend_cust_serv(p_flg_init varchar DEFAULT 'N')
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE

   proc_step       varchar := NULL; 
   nTemId     numeric := NULL;
   qta_nobilia numeric := NULL;
    vcErrore                            varchar(4000);
      vcStato                             varchar(10); -- (N)ormale / (W)arning / (E)rrore / (NA) Non Eseguito
      idProcedura                         numeric := 0;
      codProcedura                        varchar(50) := 'POST_DM_UPDT_VEND_CUST_SERV';

    v_state   TEXT;
    v_msg     TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT; 

 ANNOMESE RECORD;

begin 
    
update dwh.semaforo_cust_service
set flag_semaforo='WIP';

-- ######################################################################################
-- Log esecuzione procedura - INIZIO
-- ######################################################################################
     SELECT nextval('seq_log_proc') INTO  idProcedura;
     INSERT INTO dwh_ods.adm_log_procedure(ID_LOG, COD_PROCEDURA, DAT_INIZIO, DAT_FINE, COD_STATO, DES_LOG)
     VALUES (idProcedura, codProcedura, clock_timestamp(), NULL, 'RUN', '');

      vcStato := 'NE';

-- ######################################################################################
-- Step #0: Valorizzazioen TEM_ID
-- ######################################################################################
     IF nTemID IS NULL THEN
        SELECT MIN((TO_CHAR(CFG_DT_INF, 'YYYYMM'))::numeric )
        INTO   nTemID
        FROM   dwh_ep.sys_wkt_periodi_temporali
        WHERE  CFG_COD = 'M-1'
               AND PARTKEY < 'p004';
IF(p_flg_init='Y')
then

   -- SB 17/10/2023  : Script che aggiorna le tabelle di appoggio per il report quality del customer servcie
   truncate table dwh.venduto_per_customer_service;
  

   
   proc_step := 'LOOP ANNOMESE';
   begin 
   FOR ANNOMESE 
    IN (
         SELECT
               mf_id  FROM d_tempo WHERE mf_id
               BETWEEN To_Char(current_date-1+interval '-24 month','YYYYMM')::integer and  To_Char(current_date-1+interval '-1 month','YYYYMM')::integer              
           
  -- intervallo rolling 12 mese chiuso

       )
   LOOP
      loop statement         ;
   END LOOP;  
  end;
else
no loop statement       ;
end if; 

/* effettuo il merge per rimpiere i buchi dove articolo non viene portato avanti nel dwh */



-- ######################################################################################
-- ######################################################################################
      vcStato := 'N';

  -- ######################################################################################
-- Log esecuzione procedura - FINE OK
-- ######################################################################################
     UPDATE dwh_ods.adm_log_procedure
     SET    DAT_FINE    = clock_timestamp()
            , COD_STATO = vcStato
            , DES_LOG   = nTemID::varchar|| ' - Terminazione Corretta.'
     WHERE  ID_LOG = idProcedura;
     update dwh.semaforo_cust_service
   set flag_semaforo='OK';
    
  EXCEPTION
     WHEN OTHERS THEN
          --vcErrore := SUBSTR('EXCEPTION: SQLCODE = ' || SQLSTATE::varchar || ' - SQLERRM = ' || SQLERRM, 1, 4000);

            GET STACKED DIAGNOSTICS
                    v_state   = RETURNED_SQLSTATE,
                    v_msg     = MESSAGE_TEXT,
                    v_detail  = PG_EXCEPTION_DETAIL,
                    v_hint    = PG_EXCEPTION_HINT,
                    v_context = PG_EXCEPTION_CONTEXT; 
                
          vcErrore := SUBSTR('EXCEPTION: SQLCODE = ' || v_state || ' - SQLERRM = ' || v_msg, 1, 4000);
          vcStato := 'E';

-- ######################################################################################
-- Log esecuzione procedura - FINE KO
-- ######################################################################################
     UPDATE dwh_ods.adm_log_procedure
     SET    DAT_FINE    = clock_timestamp()
            , COD_STATO = vcStato
            , DES_LOG   = SUBSTR(nTemID::varchar || ' - ' || coalesce(DES_LOG, ' ') || vcErrore, 1, 512)
     WHERE  ID_LOG = idProcedura;
    update dwh.semaforo_cust_service
   set flag_semaforo='KO';
  
  
end ;
$function$;

  2. 0

    The main issue is missing END keyword before ELSE. This is not Pascal language, you don’t need to use explicit blocks everywhere. Try to read documentation first, please.

    Your code (wrong):

    IF x THEN
BEGIN
  RAISE NOTICE 'hello';
  a := a + 1;
END;
ELSE
BEGIN
  RAISE NOTICE 'Hi';
  a := a + 2;
END;
END IF;

    Correct version:

    IF x THEN
  RAISE NOTICE 'hello';
  a := a + 1;
ELSE
  RAISE NOTICE 'Hi';
  a := a + 2;
END IF;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search