How to pass a variable to a stored procedure? Calling the following procedure in DO $$ bloc seems to not work correctly .

Procedure :

CREATE OR REPLACE PROCEDURE pLog(P_VAR_LOG text)  
AS $$
BEGIN
  IF D_SetLog THEN


      RAISE NOTICE '[LOG %] %',to_char(sysdate,'DD/MM/YYYY - HH24:MI:SS'),P_VAR_LOG;


  END IF
;

END;
$$ LANGUAGE plpgsql;


I tried to declare the PROCEDURE in the declare bloc but it's not working.

Here is the DO $$ bloc definition : 

DO $$
DECLARE

CRITERE1 CURSOR FOR
    SELECT
            DOSSIER.ID_DOSSIER,
            DOSSIER.ED_ETAT
    FROM
        IMM_DOSSIER DOSSIER,
        IMM_CHANTIER CHANTIER
    WHERE
        DOSSIER.ID_DOSSIER = CHANTIER.ID_DOSSIER
        AND DOSSIER.ED_ETAT &lt;&gt; 'CLOS'
        AND CHANTIER.DATE_FIN_REELLE is null
        AND CHANTIER.DATE_FIN_SOUHAITEE is not null
        AND CHANTIER.DATE_FIN_SOUHAITEE &lt; LOCALTIMESTAMP

UNION

    SELECT
            DOSSIER.ID_DOSSIER,
            DOSSIER.ED_ETAT
    FROM (
            SELECT
                DEVIS.ID_DOSSIER,
                MIN(TRAVAUX.DATE_FIN_SOUHAITEE) DATE_FIN_SOUHAITEE
            FROM
                IMM_DEVIS DEVIS, IMM_TRAVAUX_DEVIS TRAVAUX
            WHERE
              DEVIS.ID_DEVIS = TRAVAUX.ID_DEVIS
              AND TRAVAUX.DATE_FIN_REELLE is null
              AND TRAVAUX.DATE_FIN_SOUHAITEE is not null
            GROUP BY DEVIS.ID_DOSSIER
        )
        DOSSIER_CIBLE,
        IMM_DOSSIER DOSSIER
    WHERE
        DOSSIER.ID_DOSSIER = DOSSIER_CIBLE.ID_DOSSIER
        AND DOSSIER.ED_ETAT &lt;&gt; 'CLOS'
        and DOSSIER_CIBLE.DATE_FIN_SOUHAITEE &lt; LOCALTIMESTAMP;

-- Def des enregistrements pour les curseurs
V_C_CRITERE1 RECORD;

V_INSERT INTEGER := 0;

D_CodeRet INTEGER;

D_SetLog    BOOLEAN;

/*****************************************************************************
**  NOM MNEMONIQUE  : pLog
*****************************************************************************/



/*****************************************************************************
**  MAIN
*****************************************************************************/
BEGIN



    D_SetLog    := TRUE
;


    CALL pLog('Suppression des anciennes alertes : ')
; 
END $$;


When i execute this sql script i get the following : 

SET
DROP SEQUENCE
WARNING:  there is no transaction in progress
COMMIT
CREATE SEQUENCE
WARNING:  there is no transaction in progress
COMMIT
CREATE PROCEDURE
DO


The NOTICE is not being displayed and can't figure out why.
There are no global variables in PostgreSQL, sou you'd have to pass d_setlog as parameter to the procedure.

An alternative is to use a custom placeholder parameter in PostgreSQL:

The calling function does

SET mylog.active = true;


and the called procedure tests

IF current_setting('mylog.active', TRUE) = 'true' THEN ...


Note that such parameters are always strings.