Visão geral dos procedimentos armazenados no Amazon Redshift - Amazon Redshift

Visão geral dos procedimentos armazenados no Amazon Redshift

Procedimentos armazenados são comumente usados para encapsular lógica de transformação e validação de dados e lógica empresarial específica. A combinação de várias etapas de SQL em um procedimento armazenado permite reduzir a comunicação entre os aplicativos e o banco de dados.

Para obter controle de acesso granular, você pode criar procedimentos armazenados para executar funções sem precisar conceder ao usuário acesso às tabelas subjacentes. Por exemplo, somente o proprietário ou um superusuário pode truncar uma tabela, e um usuário precisa de privilégios de gravação para inserir dados em uma tabela. Em vez de conceder privilégios a um usuário nas tabelas subjacentes, é possível criar um procedimento armazenado que realiza a tarefa. Depois, conceda ao usuário privilégios para executar o procedimento armazenado.

Um procedimento armazenado com o atributo de segurança DEFINER é executado com os privilégios do proprietário do procedimento armazenado. Por padrão, um procedimento armazenado tem segurança INVOKER, o que significa que o procedimento usa os privilégios do usuário que chama o procedimento.

Para criar um procedimento armazenado, use o comando CREATE PROCEDURE. Para executar um procedimento, use o comando CALL. Exemplos são apresentados posteriormente nessa seção.

nota

Alguns clientes podem exibir o seguinte erro ao criar um procedimento armazenado do Amazon Redshift.

ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$

Esse erro ocorre devido à impossibilidade do cliente analisar corretamente a instrução CREATE PROCEDURE com instruções delimitadas por ponto e vírgula e com o cifrão ($) entre aspas. Isso resulta em apenas uma parte do extrato enviado ao servidor Amazon Redshift. Geralmente esse erro pode ser resolvido usando a opção Run as batch ou Execute selected do cliente.

Por exemplo, ao usar um cliente Aginity, use a opção Run entire script as batch. Ao usar SQL Workbench/J, recomendamos a versão 124. Ao usar SQL Workbench/J versão 125, considere a possibilidade de especificar um delimitador alternativo como solução temporária.

CREATE PROCEDURE contém instruções SQL delimitadas por ponto e vírgula (;). Definir um delimitador alternativo, como uma barra (/) e colocá-lo no final da instrução CREATE PROCEDURE envia a instrução inteira ao servidor do Amazon Redshift para processamento. Veja um exemplo a seguir.

CREATE OR REPLACE PROCEDURE test()
AS $$
BEGIN
  SELECT 1 a;
END;
$$
LANGUAGE plpgsql
;
/

Para obter mais informações, consulte Delimitador alternativo na documentação do SQL Workbench/J. Ou use um cliente com melhor suporte para analisar instruções CREATE PROCEDURE, como o editor de consultas no console do Amazon Redshift ou o TablePlus.

Tópicos

O exemplo a seguir mostra um procedimento sem argumentos de saída. Por padrão, os argumentos são de entrada (IN).

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar)
AS $$
BEGIN
  RAISE INFO 'f1 = %, f2 = %', f1, f2;
END;
$$ LANGUAGE plpgsql;

call test_sp1(5, 'abc');
INFO: f1 = 5, f2 = abc
CALL

nota

Quando você escreve procedimentos armazenados, recomendamos a prática de proteger valores confidenciais:

Não codifique nenhuma informação confidencial na lógica do procedimento armazenado. Por exemplo, não atribua uma senha de usuário em uma instrução CREATE USER no corpo de um procedimento armazenado. Isso representa um risco de segurança, pois valores codificados podem ser registrados como metadados de esquema nas tabelas de catálogos. Em vez disso, transmita valores confidenciais, como senhas, como argumentos ao procedimento armazenado por meio de parâmetros.

Para obter mais informações sobre os procedimentos armazenados, consulte CREATE PROCEDURE e Criar procedimentos armazenados no Amazon Redshift. Para obter mais informações sobre tabelas de catálogos, consulte Tabelas de catálogo do sistema.

O exemplo a seguir mostra um procedimento com argumentos de saída. Os argumentos são de entrada (IN), entrada e saída (INOUT) e saída (OUT).

CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256))
AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;


call test_sp2(2,'2019');

         f2          | column2
---------------------+---------
 2019+2019+2019+2019 | 2
(1 row)