Multiple messages with send_mail function

[DSMejantel]

Hello !

I try to send a message to different user when some condition is done :

--Mailing des besoins
SELECT sqlpage.send_mail(json_object(
    'to', courriel,
    'subject', 'Alerte Barjac Mobilités',
    'body', 'Bonjour '||user_id||'. Un conducteur propose un trajet vers une de vos destinations demandées :  ' 
            || arrivee || ', le ' || strftime('%d/%m/%Y',jour) || ' pour une arrivée vers ' || heure||'.  Vous pouvez consulter https://covoiturage.barjac-en-lozere.fr pour réserver votre place.'
)) FROM alerte WHERE courriel IS NOT NULL AND courriel != '';

Its work. Each user receive a message with personal information.
But this function is verbose and we can't use the redirect component.

I try to use sqlpage.run_sql but I have the same issue.

How can I do to use this awesome new function ?

[lovasoa]

Hi ! run_sql does sound like the solution to your problem, why doesn't it work ? you can use it to summarize the results of all your send_mail calls

send_mails.sql

SELECT sqlpage.send_mail(json_object(...)) FROM myemails

show.sql

something like

SET mail_results = sqlpage.run_sql('send_mails.sql');

SELECT
    'alert' AS component,
    CASE
        WHEN failed_count > 0 THEN 'warning'
        WHEN total_count = 0 THEN 'info'
        ELSE 'success'
    END AS color,
    'Mailing result' AS title,
    printf(
        '%d recipient(s)
%d messages sent
%d errors',
        total_count,
        total_count - failed_count,
        failed_count
    ) AS description
FROM (
    SELECT
        count(*) AS total_count,
        coalesce(
            sum(((value ->> '$.result') ->> '$.status') = 'error'),
            0
        ) AS failed_count
    FROM json_each($mail_results)
);

[DSMejantel]

Hi lovasoa !
You're right. I have the soluce with sqlpage.run_sql but I have another error in my file !

So :

-- Exécution muette des envois de mail (via le fichier envoi_alerte.sql)
SET mail_results = sqlpage.run_sql('envoi_alerte.sql'); 

envoi_alerte.sql

SELECT sqlpage.send_mail(json_object(
    'to', courriel,
    'subject', 'Alerte Barjac Mobilités',
    'body', 'Bonjour '||user_id||'. Un conducteur propose un trajet vers une de vos destinations demandées :  ' 
            || arrivee || ', le ' || strftime('%d/%m/%Y',jour) || ' pour une arrivée vers ' || heure||'.  Vous pouvez consulter https://covoiturage.barjac-en-lozere.fr pour réserver votre place.'
)) FROM alerte WHERE courriel IS NOT NULL AND courriel != ''