Head over to the Supabase dashboard and pick the project you want to work on. In the sidebar, navigate to Database > Functions. Click on New Function to get started with creating a new function.

In the function editor, you'll need to write the SQL code for your function. This code will handle sending emails. Here's an example of a PL/pgSQL function that uses an external HTTP service to send emails:

CREATE OR REPLACE FUNCTION send_email(to_email TEXT, subject TEXT, message TEXT)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    request_url TEXT := 'https://api.emailservice.com/send';
    response JSON;
BEGIN
    response := (
        SELECT
            content::json
        FROM
            http_post(
                request_url,
                json_build_object(
                    'to', to_email,
                    'subject', subject,
                    'message', message
                )::text,
                'Content-Type', 'application/json'
            )
    );

    IF response->>'status' != 'success' THEN
        RAISE EXCEPTION 'Email sending failed: %', response->>'error';
    END IF;
END;
$$;

This example assumes you're using an HTTP library (http_post) that needs to be available in your PostgreSQL setup. Make sure to adjust the API endpoint and payload structure to fit the email service you're using.

If the http_post function isn't available, you might need to enable the http extension in PostgreSQL:

CREATE EXTENSION IF NOT EXISTS http;

Some PostgreSQL instances might not support HTTP libraries directly. If that's the case, consider using a cloud function or another server-side mechanism.

For security reasons, store API keys and other sensitive info as environment variables. Supabase offers secure ways to handle these variables. Configure your function to read from these environment variables to keep sensitive information out of your code.

Use the SQL editor in Supabase or a database client to run and test the function. Make sure it sends an email and handles errors properly.

Example:

SELECT send_email('[email protected]', 'Test Subject', 'Test Message');

Check the success/failure response and troubleshoot any issues as needed.

If you want the email to be sent on a specific database event, create a trigger that calls the email function when that event occurs.

Example:

CREATE TRIGGER user_sign_up
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION send_email(NEW.email, 'Welcome!', 'Thank you for signing up.');

This trigger will send a welcome email to users after they sign up.

If you're sending emails from a front-end application, configure Cross-Origin Resource Sharing (CORS) policies on your Supabase instance and email API service to allow cross-origin requests from your domain.

Keep an eye on the function’s performance and handle any issues that come up. Update security measures regularly, like refreshing API keys and maintaining the server-side logic. Perform routine checks to ensure the function stays effective and secure.