Usando um pacote de extensão da AWS SCT para emular o SQL Server Database Mail no PostgreSQL - AWS Schema Conversion Tool

As traduções são geradas por tradução automática. Em caso de conflito entre o conteúdo da tradução e da versão original em inglês, a versão em inglês prevalecerá.

Usando um pacote de extensão da AWS SCT para emular o SQL Server Database Mail no PostgreSQL

Você pode usar o SQL Server Database Mail para enviar mensagens de e-mail aos usuários a partir do mecanismo de banco de dados do SQL Server ou da instância gerenciada do Azure SQL. Essas mensagens de e-mail podem conter resultados de consultas ou incluir arquivos de qualquer recurso em sua rede. Para obter mais informações sobre o SQL Server Database Mail, consulte a documentação técnica da Microsoft.

O PostgreSQL não tem um equivalente para o SQL Server Database Mail. Para emular os atributos do SQL Server Database Mail, a AWS SCT cria um pacote de extensão. Este pacote de extensão usa a AWS Lambda o Amazon Simple Email Service (Amazon SES). A AWS Lambda fornece aos usuários uma interface para interagir com o serviço de envio de e-mail do Amazon SES. Para configurar essa interação, adicione o nome do recurso da Amazon (ARN) da sua função do Lambda.

Para uma nova conta de e-mail, use o comando a seguir.

do $$ begin PERFORM sysmail_add_account_sp ( par_account_name :='your_account_name', par_email_address := 'your_account_email', par_display_name := 'your_account_display_name', par_mailserver_type := 'AWSLAMBDA' par_mailserver_name := 'ARN' ); end; $$ language plpgsql;

Para adicionar o ARN da sua função do Lambda à conta de e-mail existente, use o comando a seguir.

do $$ begin PERFORM sysmail_update_account_sp ( par_account_name :='existind_account_name', par_mailserver_type := 'AWSLAMBDA' par_mailserver_name := 'ARN' ); end; $$ language plpgsql;

Nos exemplos anteriores, ARN é o ARN da sua função do Lambda.

Para emular o comportamento do SQL Server Database Mail no PostgreSQL, o pacote de extensão da AWS SCT usa as seguintes tabelas, visualizações e procedimentos.

Tabelas que emulam o SQL Server Database Mail no PostgreSQL

Para emular o SQL Server Database Mail, o pacote de extensão usa as seguintes tabelas:

sysmail_account

Armazena as informações sobre as contas de e-mail.

sysmail_profile

Armazena as informações sobre os perfis de usuário.

sysmail_server

Armazena as informações sobre os servidores de e-mail.

sysmail_mailitems

Armazena a lista das mensagens de e-mail.

sysmail_attachments

Contém uma linha para cada anexo de e-mail.

sysmail_log

Armazena as informações do serviço sobre o envio de mensagens de e-mail.

sysmail_profileaccount

Armazena as informações sobre os perfis de usuário e contas de e-mail.

Visualizações que emulam o SQL Server Database Mail no PostgreSQL

Para emular o SQL Server Database Mail, a AWS SCT cria as seguintes visualizações no banco de dados PostgreSQL para garantir a compatibilidade. O pacote de extensão não as usa, mas seu código convertido pode consultar essas visualizações.

sysmail_allitems

Inclui uma lista de todos os e-mails.

sysmail_faileditems

Inclui uma lista de e-mails que não puderam ser enviados.

sysmail_sentitems

Inclui uma lista de e-mails enviados.

sysmail_unsentitems

Inclui uma lista de e-mails que ainda não foram enviados.

sysmail_mailattachments

Inclui uma lista de arquivos anexados.

Procedimentos que emulam o SQL Server Database Mail no PostgreSQL

Para emular o SQL Server Database Mail, o pacote de extensão usa os seguintes procedimentos:

sp_send_dbmail

Envia um e-mail para os destinatários especificados.

sysmail_add_profile_sp

Cria um novo perfil de usuário.

sysmail_add_account_sp

Cria uma nova conta de e-mail que armazena informações como credenciais do Simple Mail Transfer Protocol (SMTP) e assim por diante.

sysmail_add_profileaccount_sp

Adiciona uma conta de e-mail ao perfil de usuário especificado.

sysmail_update_profile_sp

Altera os atributos do perfil do usuário, como descrição, nome e assim por diante.

sysmail_update_account_sp

Altera as informações na conta de e-mail existente.

sysmail_update_profileaccount_sp

Atualiza as informações da conta de e-mail no perfil de usuário especificado.

sysmail_delete_profileaccount_sp

Remove uma conta de e-mail do perfil de usuário especificado.

sysmail_delete_account_sp

Exclui a conta de e-mail.

sysmail_delete_profile_sp

Exclui o perfil de usuário.

sysmail_delete_mailitems_sp

Exclui e-mails de tabelas internas.

sysmail_help_profile_sp

Exibe informações sobre o perfil do usuário.

sysmail_help_account_sp

Exibe informações sobre a conta de e-mail.

sysmail_help_profileaccount_sp

Exibe informações sobre contas de e-mail associadas ao perfil de usuário.

sysmail_dbmail_json

Um procedimento interno que gera solicitações JSON para funções AWS Lambda.

sysmail_verify_profile_sp, sysmail_verify_account_sp, sysmail_verify_addressparams_sp

Procedimentos internos que verificam as configurações.

sp_get_dbmail, sp_set_dbmail, sysmail_dbmail_xml

Procedimentos internos obsoletos.

Sintaxe para procedimentos que emulam o SQL Server Database Mail no PostgreSQL

O procedimento aws_sqlserver_ext.sp_send_dbmail no pacote de extensão emula o procedimento msdb.dbo.sp_send_dbmail. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_profile_name varchar = NULL::character varying, par_recipients text = NULL::text, par_copy_recipients text = NULL::text, par_blind_copy_recipients text = NULL::text, par_subject varchar = NULL::character varying, par_body text = NULL::text, par_body_format varchar = NULL::character varying, par_importance varchar = 'NORMAL'::character varying, par_sensitivity varchar = 'NORMAL'::character varying, par_file_attachments text = NULL::text, par_query text = NULL::text, par_execute_query_database varchar = NULL::character varying, par_attach_query_result_as_file smallint = 0, par_query_attachment_filename varchar = NULL::character varying, par_query_result_header smallint = 1, par_query_result_width integer = 256, par_query_result_separator VARCHAR = ' '::character varying, par_exclude_query_output smallint = 0, par_append_query_error smallint = 0, par_query_no_truncate smallint = 0, par_query_result_no_padding smallint = 0, out par_mailitem_id integer, par_from_address text = NULL::text, par_reply_to text = NULL::text, out returncode integer

O procedimento aws_sqlserver_ext.sysmail_delete_mailitems_sp no pacote de extensão emula o procedimento msdb.dbo.sysmail_delete_mailitems_sp. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_sent_before timestamp = NULL::timestamp without time zone, par_sent_status varchar = NULL::character varying, out returncode integer

O procedimento aws_sqlserver_ext.sysmail_add_profile_sp no pacote de extensão emula o procedimento msdb.dbo.sysmail_add_profile_sp. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_profile_name varchar, par_description varchar = NULL::character varying, out par_profile_id integer, out returncode integer

O procedimento aws_sqlserver_ext.sysmail_add_account_sp no pacote de extensão emula o procedimento msdb.dbo.sysmail_add_account_sp. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_account_name varchar par_email_address varchar par_display_name varchar = NULL::character varying par_replyto_address varchar = NULL::character varying par_description varchar = NULL::character varying par_mailserver_name varchar = NULL::character varying par_mailserver_type varchar = 'SMTP'::bpchar par_port integer = 25 par_username varchar = NULL::character varying par_password varchar = NULL::character varying par_use_default_credentials smallint = 0 par_enable_ssl smallint = 0 out par_account_id integer out returncode integer

O procedimento aws_sqlserver_ext.sysmail_add_profileaccount_sp no pacote de extensão emula o procedimento msdb.dbo.sysmail_add_profileaccount_sp. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_profile_id integer = NULL::integer, par_profile_name varchar = NULL::character varying, par_account_id integer = NULL::integer, par_account_name varchar = NULL::character varying, par_sequence_number integer = NULL::integer, out returncode integer

O procedimento aws_sqlserver_ext.sysmail_help_profile_sp no pacote de extensão emula o procedimento msdb.dbo.sysmail_help_profile_sp. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_profile_id integer = NULL::integer, par_profile_name varchar = NULL::character varying, out returncode integer

O procedimento aws_sqlserver_ext.sysmail_update_profile_sp no pacote de extensão emula o procedimento msdb.dbo.sysmail_update_profile_sp. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_profile_id integer = NULL::integer, par_profile_name varchar = NULL::character varying, par_description varchar = NULL::character varying, out returncode integer

O procedimento aws_sqlserver_ext.sysmail_delete_profile_sp no pacote de extensão emula o procedimento msdb.dbo.sysmail_delete_profile_sp. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_profile_id integer = NULL::integer, par_profile_name varchar = NULL::character varying, par_force_delete smallint = 1, out returncode integer

O procedimento aws_sqlserver_ext.sysmail_help_account_sp no pacote de extensão emula o procedimento msdb.dbo.sysmail_help_account_sp. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_account_id integer = NULL::integer, par_account_name varchar = NULL::character varying, out returncode integer

O procedimento aws_sqlserver_ext.sysmail_update_account_sp no pacote de extensão emula o procedimento msdb.dbo.sysmail_update_account_sp. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_account_id integer = NULL::integer, par_account_name varchar = NULL::character varying, par_email_address varchar = NULL::character varying, par_display_name varchar = NULL::character varying, par_replyto_address varchar = NULL::character varying, par_description varchar = NULL::character varying, par_mailserver_name varchar = NULL::character varying, par_mailserver_type varchar = NULL::character varying, par_port integer = NULL::integer, par_username varchar = NULL::character varying, par_password varchar = NULL::character varying, par_use_default_credentials smallint = NULL::smallint, par_enable_ssl smallint = NULL::smallint, par_timeout integer = NULL::integer, par_no_credential_change smallint = NULL::smallint, out returncode integer

O procedimento aws_sqlserver_ext.sysmail_delete_account_sp no pacote de extensão emula o procedimento msdb.dbo.sysmail_delete_account_sp. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_account_id integer = NULL::integer, par_account_name varchar = NULL::character varying, out returncode integer

O procedimento aws_sqlserver_ext.sysmail_help_profileaccount_sp no pacote de extensão emula o procedimento msdb.dbo.sysmail_help_profileaccount_sp. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_profile_id integer = NULL::integer, par_profile_name varchar = NULL::character varying, par_account_id integer = NULL::integer, par_account_name varchar = NULL::character varying, out returncode integer

O procedimento aws_sqlserver_ext.sysmail_update_profileaccount_sp no pacote de extensão emula o procedimento msdb.dbo.sysmail_update_profileaccount_sp. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_profile_id integer = NULL::integer, par_profile_name varchar = NULL::character varying, par_account_id integer = NULL::integer, par_account_name varchar = NULL::character varying, par_sequence_number integer = NULL::integer, out returncode integer

O procedimento aws_sqlserver_ext.sysmail_delete_profileaccount_sp no pacote de extensão emula o procedimento msdb.dbo.sysmail_delete_profileaccount_sp. Para obter mais informações sobre o procedimento do SQL Server Database Mail de origem, consulte a documentação técnica da Microsoft.

par_profile_id integer = NULL::integer, par_profile_name varchar = NULL::character varying, par_account_id integer = NULL::integer, par_account_name varchar = NULL::character varying, out returncode integer

Exemplos de uso de procedimentos que emulam o SQL Server Database Mail no PostgreSQL

Para enviar um e-mail, use o procedimento aws_sqlserver_ext.sp_send_dbmail conforme mostrado a seguir.

PERFORM sp_send_dbmail ( par_profile_name := 'Administrator', par_recipients := 'hello@rusgl.info', par_subject := 'Automated Success Message', par_body := 'The stored procedure finished' );

O exemplo a seguir mostra como enviar um e-mail com resultados da consulta.

PERFORM sp_send_dbmail ( par_profile_name := 'Administrator', par_recipients := 'hello@rusgl.info', par_subject := 'Account with id = 1', par_query := 'SELECT COUNT(*)FROM Account WHERE id = 1' );

O exemplo a seguir mostra como enviar e-mails brutos com código HTML.

DECLARE var_tableHTML TEXT; SET var_tableHTML := CONCAT( '<H1>Work Order Report</H1>', '<table border="1">', '<tr><th>Work Order ID</th><th>Product ID</th>', '<th>Name</th><th>Order Qty</th><th>Due Date</th>', '<th>Expected Revenue</th></tr>', '</table>' ); PERFORM sp_send_dbmail ( par_recipients := 'hello@rusgl.info', par_subject := 'Work Order List', par_body := var_tableHTML, par_body_format := 'HTML' );

Para excluir e-mails, use o procedimento aws_sqlserver_ext.sysmail_delete_mailitems_sp conforme mostrado a seguir.

DECLARE var_GETDATE datetime; SET var_GETDATE = NOW(); PERFORM sysmail_delete_mailitems_sp ( par_sent_before := var_GETDATE );

O exemplo a seguir mostra como excluir os e-mails mais antigos.

PERFORM sysmail_delete_mailitems_sp ( par_sent_before := '31.12.2015' );

O exemplo a seguir mostra como excluir todos os e-mails que não podem ser enviados.

PERFORM sysmail_delete_mailitems_sp ( par_sent_status := 'failed' );

Para criar um novo perfil de usuário, use o procedimento aws_sqlserver_ext.sysmail_add_profile_sp conforme mostrado a seguir.

PERFORM sysmail_add_profile_sp ( profile_name := 'Administrator', par_description := 'administrative mail' );

O exemplo a seguir mostra como criar um novo perfil e salvar o identificador de perfil exclusivo em uma variável.

DECLARE var_profileId INT; SELECT par_profile_id FROM sysmail_add_profile_sp ( profile_name := 'Administrator', par_description := ' Profile used for administrative mail.') INTO var_profileId; SELECT var_profileId;

Para criar uma nova conta de e-mail, use o procedimento aws_sqlserver_ext.sysmail_add_account_sp conforme mostrado a seguir.

PERFORM sysmail_add_account_sp ( par_account_name :='Audit Account', par_email_address := 'dba@rusgl.info', par_display_name := 'Test Automated Mailer', par_description := 'Account for administrative e-mail.', par_mailserver_type := 'AWSLAMBDA' par_mailserver_name := 'arn:aws:lambda:us-west-2:555555555555:function:pg_v3' );

Para adicionar uma conta de e-mail ao perfil do usuário, use o procedimento aws_sqlserver_ext.sysmail_add_profileaccount_sp conforme mostrado a seguir.

PERFORM sysmail_add_profileaccount_sp ( par_account_name := 'Administrator', par_account_name := 'Audit Account', par_sequence_number := 1 );

Use exemplos de casos para emular o SQL Server Database Mail no PostgreSQL

Se o código do banco de dados de origem usa o SQL Server Database Mail para enviar e-mails, você pode usar o pacote de extensão da AWS SCT para converter esse código em PostgreSQL.

Para enviar um e-mail do seu banco de dados PostgreSQL
  1. Crie e configure sua função AWS Lambda.

  2. Aplicar o pacote de extensão da AWS SCT

  3. Crie um perfil de usuário usando a função sysmail_add_profile_sp conforme mostrado a seguir.

  4. Crie uma conta de e-mail usando a função sysmail_add_account_sp conforme mostrado a seguir.

  5. Adicione essa conta de e-mail ao seu perfil de usuário usando a função sysmail_add_profileaccount_sp mostrada a seguir.

    CREATE OR REPLACE FUNCTION aws_sqlserver_ext. proc_dbmail_settings_msdb() RETURNS void AS $BODY$ BEGIN PERFORM aws_sqlserver_ext.sysmail_add_profile_sp( par_profile_name := 'Administrator', par_description := 'administrative mail' ); PERFORM aws_sqlserver_ext.sysmail_add_account_sp( par_account_name := 'Audit Account', par_description := 'Account for administrative e-mail.', par_email_address := 'dba@rusgl.info', par_display_name := 'Test Automated Mailer', par_mailserver_type := 'AWSLAMBDA' par_mailserver_name := 'your_ARN' ); PERFORM aws_sqlserver_ext.sysmail_add_profileaccount_sp( par_profile_name := 'Administrator', par_account_name := 'Audit Account', par_sequence_number := 1 ); END; $BODY$ LANGUAGE plpgsql;
  6. Envie um e-mail usando a função sp_send_dbmail, conforme mostrado a seguir.

    CREATE OR REPLACE FUNCTION aws_sqlserver_ext. proc_dbmail_send_msdb() RETURNS void AS $BODY$ BEGIN PERFORM aws_sqlserver_ext.sp_send_dbmail( par_profile_name := 'Administrator', par_recipients := 'hello@rusgl.info', par_body := 'The stored procedure finished', par_subject := 'Automated Success Message' ); END; $BODY$ LANGUAGE plpgsql;

Para ver as informações sobre todos os perfis de usuário, use o procedimento sysmail_help_profile_sp conforme mostrado a seguir.

SELECT FROM aws_sqlserver_ext.sysmail_help_profile_sp();

O exemplo a seguir exibe as informações sobre o perfil de usuário específico.

select from aws_sqlserver_ext.sysmail_help_profile_sp(par_profile_id := 1); select from aws_sqlserver_ext.sysmail_help_profile_sp(par_profile_name := 'Administrator');

Para visualizar as informações sobre todas as contas de e-mail, use o procedimento sysmail_help_account_sp conforme mostrado a seguir.

select from aws_sqlserver_ext.sysmail_help_account_sp();

O exemplo a seguir exibe as informações sobre a conta de e-mail específica.

select from aws_sqlserver_ext.sysmail_help_account_sp(par_account_id := 1); select from aws_sqlserver_ext.sysmail_help_account_sp(par_account_name := 'Audit Account');

Para visualizar as informações sobre todas as contas de e-mail que estão associadas com os perfis de usuário, use o procedimento sysmail_help_profileaccount_sp conforme mostrado a seguir.

select from aws_sqlserver_ext.sysmail_help_profileaccount_sp();

O exemplo a seguir filtra os registros por identificador, nome do perfil ou nome da conta.

select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_profile_id := 1); select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_profile_id := 1, par_account_id := 1); select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_profile_name := 'Administrator'); select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_account_name := 'Audit Account');

Para alterar o nome ou a descrição do perfil do usuário, use o procedimento sysmail_update_profile_sp conforme mostrado a seguir.

select aws_sqlserver_ext.sysmail_update_profile_sp( par_profile_id := 2, par_profile_name := 'New profile name' );

Para alterar as configurações da conta de e-mail, use o procedimento ysmail_update_account_sp conforme mostrado a seguir.

select from aws_sqlserver_ext.sysmail_update_account_sp ( par_account_name := 'Audit Account', par_mailserver_name := 'arn:aws:lambda:region:XXXXXXXXXXXX:function:func_test', par_mailserver_type := 'AWSLAMBDA' );