retrieve values of column and use them inside procedure postgresql-CodePudding

I have developped a procedure that sends mails to people, my idea is to send mails to every receipent stored in my database (I have a column named mail_consultation that stores each recipient mail) :

mail_consultation
[email protected]
[email protected]
[email protected]

My procedure below loops on my table but I don't know how to retrieve the values of my column (mail_consultation) then use them inside pgmail(). I tried that :

DECLARE
    i RECORD;
BEGIN

FOR i IN 
    SELECT date_debut, statut, CURRENT_DATE, mail_consultation
    FROM fond_plan.arretes_de_voiries_evw
    WHERE date(date_debut) = CURRENT_DATE
    AND statut = 'En instruction collectivité'
    
    LOOP
        perform pgmail('[email protected]', mail_consultation, 'test if it works', 'Hello', '');
    END LOOP;
END;

I get the following error :
ERROR: ERROR: column "mail_consultation" does not exist

CodePudding user response：

You are fetching the row into a record variable called i, so you have to retrieve it from there:

perform pgmail(
   '[email protected]',
   i.mail_consultation,
   'test if it works',
   'Hello', ''
);