CREATE PROCEDURE

Cria um novo procedimento armazenado ou substitui um procedimento existente para o banco de dados atual.

Para ter mais informações e exemplos, consulte Criar procedimentos armazenados no Amazon Redshift.

Privilégios obrigatórios

Você deve ter permissão de uma das seguintes formas para executar CREATE OR REPLACE PROCEDURE:

• Para CREATE PROCEDURE:

  • Superusuário

  • Usuários com privilégios CREATE e USAGE no esquema no qual o procedimento armazenado é criado.

• Para REPLACE PROCEDURE:

  • Superusuário

  • Proprietário do procedimento

Sintaxe

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name  
  ( [ [ argname ] [ argmode ] argtype [, ...] ] )
[ NONATOMIC ]
AS $$
  procedure_body
$$ LANGUAGE plpgsql
[ { SECURITY INVOKER | SECURITY DEFINER } ]
[ SET configuration_parameter { TO value | = value } ]

Parâmetros

OR REPLACE

Uma cláusula que especifica que, se um procedimento com o mesmo nome e tipos de dados do argumento de entrada, ou assinatura, como esse já existir, o procedimento existente será substituído. Só é possível substituir um procedimento por um novo procedimento que defina um conjunto idêntico de tipos de dados.

Se você definir um procedimento com o mesmo nome de um procedimento existente, mas com uma assinatura diferente, um novo procedimento será criado. Em outras palavras, o nome do procedimento é sobrecarregado. Para obter mais informações, consulte Sobrecarga de nomes de procedimento.

sp_procedure_name

O nome do procedimento. Se você especificar um nome de esquema (como myschema.myprocedure), o procedimento será criado no esquema especificado. Caso contrário, o procedimento será criado no esquema atual. Para obter mais informações sobre nomes válidos, consulte Nomes e identificadores.

Recomendamos que você prefixe os nomes de todos os UDF com sp_. O Amazon Redshift reserva o prefixo sp_ para nomes de procedimentos armazenados. Ao usar o prefixo sp_, você garante que o seu nome de procedimento armazenado não entra em conflito com qualquer procedimento armazenado integrado ao Amazon Redshift existente ou futuro ou nomes de função. Para obter mais informações, consulte Nomeação de procedimentos armazenados.

É possível definir mais de um procedimento com o mesmo nome se os tipos de dados dos argumentos de entrada, ou assinaturas, forem diferentes. Em outras palavras, nesse caso, o nome do procedimento é sobrecarregado. Para obter mais informações, consulte Sobrecarga de nomes de procedimento

[argname] [ argmode] argtype

Uma lista de nomes de argumentos, modos de argumentos e tipos de dados. Somente o tipo de dados é obrigatório. O nome e o modo são opcionais e suas posições podem ser alternadas.

O modo do argumento pode ser IN, OUT ou INOUT. O padrão é IN.

Use argumentos OUT e INOUT para retornar um ou mais valores de uma chamada de procedimento. Quando houver argumentos OUT ou INOUT, a chamada de procedimento retorna uma linha de resultados que contém n colunas, onde n é o número total de argumentos OUT ou INOUT.

Argumentos INOUT são argumentos de entrada e saída ao mesmo tempo. Argumentos de entrada incluem argumentos IN e INOUT, e argumentos de saída incluem argumentos OUT e INOUT.

Argumentos OUT não são especificados como parte da instrução CALL. Especifique argumentos INOUT na instrução CALL do procedimento armazenado. Argumentos INOUT podem ser úteis ao enviar e retornar valores de uma chamada aninhada, bem como ao retornar um refcursor. Para obter mais informações sobre os tipos refcursor, consulte Cursores.

Os tipos de dados de argumento podem ser qualquer tipo de dados padrão do Amazon Redshift. Além disso, um tipo de dados de argumento pode ser refcursor.

Você pode especificar no máximo 32 argumentos de entrada e 32 argumentos de saída.

AS $$ procedure_body $$

Uma construção que delimita o procedimento a ser executado. As palavras-chave literais AS $$ e $$ são obrigatórias.

O Amazon Redshift exige que você inclua a instrução em seu procedimento usando um formato chamado cotação de dólar. Qualquer item incluído na instrução é aprovado exatamente como está. Não é preciso inserir caracteres de escape antes dos caracteres especiais, pois o conteúdo da string é gravado literalmente.

Com a cotação de dólar, um par de símbolos de cifrão ($$) deve ser usado no início e no final da instrução para que ela seja executada, conforme exibido no exemplo a seguir.

$$ my statement $$

Como opção, entre cada par de símbolos de cifrão, você pode especificar uma string para ajudar a identificar a instrução. A string que você usa deve ser a mesma no início e no final dos pares de sinais. A sequência diferencia maiúsculas de minúsculas e segue as mesmas restrições de um identificador sem aspas, com a exceção de que não pode conter cifrões. O exemplo a seguir usa a string test.

$test$ my statement $test$

Essa sintaxe também é útil para cotação de dólar aninhada. Para obter mais informações sobre o uso de símbolos de cifrão, consulte “Constantes de string entre símbolos de cifrão” em Estrutura lexical na documentação do PostgreSQL.

procedure_body

Um conjunto de instruções válidas da PL/pgSQL. As instruções da PL/pgSQL aumentam comandos SQL com construções processuais, incluindo loops e expressões condicionais, a fim de controlar o fluxo lógico. A maioria dos comandos SQL podem ser usados no corpo do procedimento, incluindo linguagem de modificação de dados (DML), como COPY, UNLOAD e INSERT, e linguagem de definição de dados (DDL), como CREATE TABLE. Para obter mais informações, consulte Referência da linguagem PL/pgSQL.

LANGUAGE plpgsql

Um valor de linguagem. Especifique plpgsql. É necessário ter permissão de uso na linguagem para usar plpgsql. Para ter mais informações, consulte GRANT.

NONATOMIC

Cria o procedimento armazenado em um modo de transação não atômico. O modo NONATOMIC confirma automaticamente as instruções dentro do procedimento. Além disso, quando ocorre um erro dentro do procedimento NONATOMIC, o erro não será relançado se for tratado por um bloco de exceções. Para ter mais informações, consulte Gerenciamento de transações e RAISE.

Ao definir um procedimento armazenado como NONATOMIC, considere o seguinte:

• Quando você aninha chamadas de procedimentos armazenados, todos os procedimentos devem ser criados no mesmo modo de transação.

• A opção SECURITY DEFINER e a opção SET configuration_parameter não são compatíveis ao criar um procedimento no modo NONATOMIC.

• Qualquer cursor aberto (explícita ou implicitamente) será fechado automaticamente quando uma confirmação implícita for processada. Portanto, você deve abrir uma transação explícita antes de iniciar um loop de cursor para garantir que nenhuma instrução SQL dentro da iteração do loop seja confirmada implicitamente.

SECURITY INVOKER | SECURITY DEFINER

A opção SECURITY DEFINER não é compatível quando NONATOMIC é especificado.

O modo de segurança para o procedimento determina os privilégios de acesso do procedimento em tempo de execução. O procedimento deve ter permissão para acessar os objetos de banco de dados subjacentes.

Para o modo SECURITY INVOKER, o procedimento usa os privilégios do usuário que chama o procedimento. O usuário deve ter permissões explícitas para os objetos de banco de dados subjacentes. O padrão é SECURITY INVOKER.

Para o modo SECURITY DEFINER, o procedimento usa os privilégios do proprietário do procedimento. O proprietário do procedimento é definido como o usuário que possui o procedimento no tempo de execução, não necessariamente o usuário que definiu o procedimento inicialmente. O usuário que chama o procedimento precisa ter privilégio de execução no procedimento, mas não precisa de qualquer privilégio nos objetos subjacentes.

SET configuration_parameter { TO value | = value }

Essas opções não são compatíveis quando NONATOMIC é especificado.

A cláusula SET faz com que o configuration_parameter especificado seja definido como o valor especificado quando o procedimento for inserido. Depois, essa cláusula restaurará configuration_parameter para seu valor anterior quando o procedimento sair.

Observações de uso

Se um procedimento armazenado tiver sido criado usando a opção SECURITY DEFINER, ao invocar a função CURRENT_USER de dentro do procedimento armazenado, o Amazon Redshift retornará o nome de usuário do proprietário do procedimento armazenado.

Exemplos

nota

Se você encontrar um erro na execução desses exemplos parecido com:

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

Consulte Visão geral dos procedimentos armazenados no Amazon Redshift.

O exemplo a seguir cria um procedimento com dois parâmetros de entrada.

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar(20))
AS $$
DECLARE
  min_val int;
BEGIN
  DROP TABLE IF EXISTS tmp_tbl;
  CREATE TEMP TABLE tmp_tbl(id int);
  INSERT INTO tmp_tbl values (f1),(10001),(10002);
  SELECT INTO min_val MIN(id) FROM tmp_tbl;
  RAISE INFO 'min_val = %, f2 = %', min_val, f2;
END;
$$ LANGUAGE plpgsql;

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 do catálogo. 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álogo, consulte “Tabelas de catálogo do sistema”.

O exemplo a seguir cria um procedimento com um parâmetro IN, um parâmetro OUT e um parâmetro INOUT.

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;