Instruções da PL/pgSQL compatíveis - Amazon Redshift
AtribuiçãoSELECT INTONo-opSQL dinâmicoReturnCondicionais: IF Condicionais: CASELoopsCursoresRAISEControle da transação

Instruções da PL/pgSQL compatíveis

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 pode ser usada, incluindo linguagem de manipulação de dados (DML), como COPY, UNLOAD e INSERT, e linguagem de definição de dados (DDL), como CREATE TABLE. Para obter a lista dos comandos SQL abrangentes, consulte Comandos SQL. Além disso, as seguintes instruções PL/pgSQL são compatíveis com o Amazon Redshift.

Atribuição

A instrução de atribuição atribui um valor a uma variável. A expressão deve retornar um único valor.

identifier := expression;

Usar o = não padrão para a atribuição, em vez do :=, também é aceito.

Se o tipo de dados da expressão não corresponder ao tipo de dados da variável ou a variável tiver um tamanho ou uma precisão, o valor do resultado será convertido implicitamente.

Veja a seguir alguns exemplos.

customer_number := 20;
tip := subtotal * 0.15;

SELECT INTO

A instrução SELECT INTO atribui o resultado de várias colunas (mas somente uma linha) a uma variável de registro ou uma lista de variáveis escalares.

SELECT INTO target select_expressions FROM ...;

Na sintaxe anterior, target pode ser uma variável de registro ou uma lista separada por vírgulas de variáveis simples e campos de registro. A lista select_expressions e o restante do comando são iguais ao SQL regular.

Se uma lista de variáveis for usada como target, os valores selecionados deverão corresponder exatamente à estrutura do destino, caso contrário, ocorrerá um erro de tempo de execução. Quando uma variável de registro for o destino, ela se configura automaticamente para o tipo de linha das colunas do resultado da consulta.

A cláusula INTO pode aparecer praticamente em qualquer lugar na instrução SELECT. Normalmente ela aparece logo após a cláusula SELECT ou imediatamente antes da cláusula FROM. Ou seja, ela aparece imediatamente antes ou logo após a lista select_expressions.

Se a consulta não retornar nenhuma linha, valores NULL serão atribuídos a target. Se a consulta retornar várias linhas, a primeira linha será atribuída a target e o restante é descartado. A menos que a instrução contenha um ORDER BY, a primeira linha é não determinística.

Para determinar se a atribuição retornou pelo menos uma fila, use a variável especial FOUND.

SELECT INTO customer_rec * FROM cust WHERE custname = lname;
IF NOT FOUND THEN
  RAISE EXCEPTION 'employee % not found', lname;
END IF;

Para testar se um resultado de registro é nulo, use o condicional IS NULL. Não é possível determinar se outras linhas adicionais foram descartadas. O exemplo a seguir lida com o caso em que nenhuma linha foi retornada.

CREATE OR REPLACE PROCEDURE select_into_null(return_webpage OUT varchar(256))
AS $$
DECLARE
  customer_rec RECORD;
BEGIN
  SELECT INTO customer_rec * FROM users WHERE user_id=3;
  IF customer_rec.webpage IS NULL THEN
    -- user entered no webpage, return "http://"
    return_webpage = 'http://';
  END IF;
END;
$$ LANGUAGE plpgsql;

No-op

A instrução no-op (NULL;) é uma instrução de espaço reservado que não faz nada. Uma instrução no-op pode indicar que uma ramificação de uma cadeia IF-THEN-ELSE está vazia.

NULL;

SQL dinâmico

Para gerar comandos dinâmicos que podem involver diferentes tabelas ou diferentes tipos de dados toda vez que forem executados a partir de um procedimento armazenado em PL/pgSQL, use a instrução EXECUTE.

EXECUTE command-string [ INTO target ];

Acima, command-string é uma expressão que gera uma string (do tipo texto) que contém o comando a ser executado. Esse valor de command-string é enviado ao mecanismo SQL. Nenhuma substituição das variáveis em PL/pgSQL é feita na string de comando. Os valores das variáveis deve ser inserido na string de comando à medida que é construída.

nota

Não é possível usar as instruções COMMIT e ROLLBACK em SQL dinâmico. Para obter informações sobre como usar as instruções COMMIT e ROLLBACK em um procedimento armazenado, consulte Gerenciamento de transações.

Ao trabalhar com comandos dinâmicos, é usual precisar lidar com o escape de aspas simples. Recomendamos colocar texto fixo entre aspas no corpo da função usando cotação de dólar. Valores dinâmicos a serem inseridos em uma consulta construída exigem tratamento especial, pois eles mesmos podem conter aspas. O exemplo a seguir assume cotação de dólar para a função como um todo, para que as aspas não precisem ser duplicadas.

EXECUTE 'UPDATE tbl SET '
  || quote_ident(colname)
  || ' = '
  || quote_literal(newvalue)
  || ' WHERE key = '
  || quote_literal(keyvalue);

O exemplo anterior mostra as funções quote_ident(text) e quote_literal(text). Esse exemplo envia variáveis que contém identificadores de coluna e tabela para a função quote_ident. Ele também envia variáveis que contém strings literais no comando construído para a função quote_literal. Ambas as funções executam as etapas apropriadas para retornar o texto de entrada entre aspas duplas ou simples, respectivamente, com quaisquer caracteres especiais incorporados com escape adequado.

A cotação de dólar só é útil para a cotação de texto fixo. Não grave o exemplo anterior no formato a seguir.

EXECUTE 'UPDATE tbl SET '
  || quote_ident(colname)
  || ' = $$'
  || newvalue
  || '$$ WHERE key = '
  || quote_literal(keyvalue);

Não faça isso pois o exemplo trava se o conteúdo de newvalue conter $$. O mesmo problema se aplica a qualquer outro delimitador de cotação de dólar que venha a escolher. Para citar com segurança um texto não conhecido previamente, use a função quote_literal.

Return

A instrução RETURN retorna para o chamador a partir de um procedimento armazenado.

RETURN;

Por exemplo:

CREATE OR REPLACE PROCEDURE return_example(a int)
AS $$  
BEGIN
  FOR b in 1..10 LOOP
    IF b < a THEN
      RAISE INFO 'b = %', b;
    ELSE
      RETURN;
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Condicionais: IF

A instrução condicional IF pode assumir as seguintes formas na linguagem PL/pgSQL que o Amazon Redshift usa:

  • IF ... THEN

    IF boolean-expression THEN
  statements
END IF;

    Por exemplo:

    IF v_user_id <> 0 THEN
  UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

  • IF ... THEN ... ELSE

    IF boolean-expression THEN
  statements
ELSE
  statements
END IF;

    Por exemplo:

    IF parentid IS NULL OR parentid = ''
THEN
  return_name = fullname;
  RETURN;
ELSE
  return_name = hp_true_filename(parentid) || '/' || fullname;
  RETURN;
END IF;

  • IF ... THEN ... ELSIF ... THEN ... ELSE

    A palavra-chave ELSIF também pode ser escrita como ELSEIF.

    IF boolean-expression THEN
  statements
[ ELSIF boolean-expression THEN
  statements
[ ELSIF boolean-expression THEN
  statements
    ...] ]
[ ELSE
  statements ]
END IF;

    Por exemplo:

    IF number = 0 THEN
  result := 'zero';
ELSIF number > 0 THEN
  result := 'positive';
ELSIF number < 0 THEN
  result := 'negative';
ELSE
  -- the only other possibility is that number is null
  result := 'NULL';
END IF;

Condicionais: CASE

A instrução condicional CASE pode assumir as seguintes formas na linguagem PL/pgSQL que o Amazon Redshift usa:

  • CASE simples 

    CASE search-expression
WHEN expression [, expression [ ... ]] THEN
  statements
[ WHEN expression [, expression [ ... ]] THEN
  statements
  ... ]
[ ELSE
  statements ]
END CASE;

    Uma instrução CASE simples oferece execução condicional com base na igualdade dos operandos.

    O valor search-expression é avaliado uma vez e comparado sucessivamente com cada expression nas cláusulas WHEN. Se uma correspondência for encontrada, as statements correspondentes são executadas e o controle passa para a próxima instrução após END CASE. As expressões WHEN subsequentes não são avaliadas. Se nenhuma correspondência for encontrada, as statements ELSE são executadas. No entanto, se ELSE não estiver presente, uma exceção CASE_NOT_FOUND será gerada.

    Por exemplo:

    CASE x
WHEN 1, 2 THEN
  msg := 'one or two';
ELSE
  msg := 'other value than one or two';
END CASE;

  • CASE pesquisado 

    CASE
WHEN boolean-expression THEN
  statements
[ WHEN boolean-expression THEN
  statements
  ... ]
[ ELSE
  statements ]
END CASE;

    A formato pesquisado de CASE oferece execução condicional baseada na veracidade de expressões boolianas.

    A boolean-expression de cada cláusula WHEN é, por sua vez, avaliada até uma verdadeira ser encontrada. Depois, as instruções correspondentes são executadas e o controle passa para a próxima instrução após END CASE. As expressions WHEN subsequentes não são avaliadas. Se nenhum resultado verdadeiro for encontrado, as statements ELSE são executadas. No entanto, se ELSE não estiver presente, uma exceção CASE_NOT_FOUND será gerada.

    Por exemplo:

    CASE
WHEN x BETWEEN 0 AND 10 THEN
  msg := 'value is between zero and ten';
WHEN x BETWEEN 11 AND 20 THEN
  msg := 'value is between eleven and twenty';
END CASE;

Loops

As instruções de loop podem assumir os seguintes formatos na linguagem PL/pgSQL que o Amazon Redshift usa:

  • Loop simples 

    [<<label>>]
LOOP
  statements
END LOOP [ label ];

    Um loop simples define um loop não condicional que é repetido indefinidamente até ser encerrado por uma instrução EXIT ou RETURN. O rótulo opcional pode ser usado pelas instruções EXIT e CONTINUE dentro de loops aninhados para especificar a qual loop as instruções EXIT e CONTINUE se referem.

    Por exemplo:

    CREATE OR REPLACE PROCEDURE simple_loop()
LANGUAGE plpgsql
AS $$
BEGIN
  <<simple_while>>
  LOOP
    RAISE INFO 'I am raised once';  
    EXIT simple_while;
    RAISE INFO 'I am not raised';
  END LOOP;
  RAISE INFO 'I am raised once as well';
END;
$$;

  • Loop de saída

    EXIT [ label ] [ WHEN expression ];

    Se label não estiver presente, o loop mais interno será encerrado e a instrução após END LOOP será executada na sequência. Se label estiver presente, deverá ser o rótulo do loop atual ou de algum outro nível de loop ou bloco aninhado. Depois, o loop ou bloco nomeado será encerrado e o controle continuará com a instrução após END do loop ou bloco correspondente.

    Se WHEN for especificada, a saída do loop ocorrerá apenas se a expression for verdadeira. Caso contrário, o controle passa para a instrução após EXIT.

    Você pode usar EXIT com todos os tipos de loops, seu uso não se limita a loops não condicionais.

    Quando usado com um bloco BEGIN, EXIT passa o controle para a próxima instrução após o final do bloco. Um rótulo pode ser usado para essa finalidade. Um EXIT sem rótulo nunca corresponderá a um bloco BEGIN.

    Por exemplo:

    CREATE OR REPLACE PROCEDURE simple_loop_when(x int)
LANGUAGE plpgsql
AS $$
DECLARE i INTEGER := 0;
BEGIN
  <<simple_loop_when>>
  LOOP
    RAISE INFO 'i %', i;
    i := i + 1;
    EXIT simple_loop_when WHEN (i >= x);
  END LOOP;
END;
$$;

  • Loop de continuação 

    CONTINUE [ label ] [ WHEN expression ];

    Se label não for fornecido, a execução pulará para a próxima iteração do loop mais interno. Ou seja, todas as instruções restantes no corpo do loop serão ignoradas. Depois, o controle retornará para a expressão de controle do loop (se houver) a fim de determinar se há necessidade de outra iteração do loop. Se label estiver presente, ele especifica o rótulo do loop cuja execução será continuada.

    Se WHEN for especificado, a próxima iteração do loop começará somente se a expression for verdadeira. Caso contrário, o controle passa para a instrução após CONTINUE.

    Você pode usar CONTINUE com todos os tipos de loops, seu uso não se limita a loops não condicionais.

    CONTINUE mylabel;

  • Loop WHILE 

    [<<label>>]
WHILE expression LOOP
  statements
END LOOP [ label ];

    A instrução WHILE repete uma sequência de instruções até a boolean-expression ser avaliada como verdadeira. A expressão é verificada imediatamente antes de cada entrada no corpo do loop.

    Por exemplo:

    WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
  -- some computations here
END LOOP;

WHILE NOT done LOOP
  -- some computations here
END LOOP;

  • Loop FOR (variante inteiro) 

    [<<label>>]
FOR name IN [ REVERSE ] expression .. expression LOOP
  statements
END LOOP [ label ];

    O loop FOR (variante inteiro) cria um loop que itera sobre um intervalo de valores inteiros. O nome da variável é definido automaticamente como o tipo inteiro e exite somente dentro do loop. Qualquer definição existente do nome da variável será ignorada dentro do loop. As duas expressões que fornecem os limites superior e inferior do intervalo são avaliadas uma vez ao entrar no loop. Se você especificar REVERSE, o valor da etapa será subtraído, em vez de adicionado, após cada iteração.

    Se o limite inferior for maior que o limite superior (ou menor no caso REVERSE), o corpo do loop não será executado. Nenhum erro será gerado.

    Se um rótulo for anexado ao loop FOR, você pode referenciar a variável de loop inteira com um nome qualificado, usando esse rótulo.

    Por exemplo:

    FOR i IN 1..10 LOOP
  -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;

FOR i IN REVERSE 10..1 LOOP
  -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;

  • Loop FOR (variante conjunto de resultados) 

    [<<label>>]
FOR target IN query LOOP
  statements
END LOOP [ label ];

    O target é uma variável de registro ou lista separada por vírgulas de variáveis escalares. O destino recebe todas as linhas resultantes da consulta e o corpo do loop é executado para cada linha.

    O loop FOR (variante conjunto de resultados) habilita a iteração de um procedimento armazenado pelos resultados de uma consulta e manipula os dados de acordo.

    Por exemplo:

    CREATE PROCEDURE cs_refresh_reports() AS $$
DECLARE
  reports RECORD;
BEGIN
  FOR reports IN SELECT * FROM cs_reports ORDER BY sort_key LOOP
    -- Now "reports" has one record from cs_reports
    EXECUTE 'INSERT INTO ' || quote_ident(reports.report_name) || ' ' || reports.report_query;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

  • Loop FOR com SQL dinâmico

    [<<label>>]
FOR record_or_row IN EXECUTE text_expression LOOP 
  statements
END LOOP;

    Um loop FOR com SQL dinâmico habilita a iteração de um procedimento armazenado pelos resultados de uma consulta dinâmica e manipula os dados de acordo.

    Por exemplo:

    CREATE OR REPLACE PROCEDURE for_loop_dynamic_sql(x int)
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  query text;
BEGIN
  query := 'SELECT * FROM tbl_dynamic_sql LIMIT ' || x;
  FOR rec IN EXECUTE query
  LOOP
    RAISE INFO 'a %', rec.a;
  END LOOP;
END;
$$;

Cursores

Em vez de executar uma consulta completa de uma só vez, é possível definir um cursor. Um cursor encapsula uma consulta e lê o resultado da consulta algumas linhas por vez. Um dos motivos para fazer isso é evitar o estouro de memória quando o resultado contiver um grande número de linhas. Outro motivo é retornar a referência a um cursor criado por um procedimento armazenado, o que permite ao chamador ler as linhas. Essa abordagem oferece uma maneira eficiente de retornar grandes conjuntos de linhas de procedimentos armazenados.

Para usar cursores em um procedimento armazenado NONATOMIC, coloque o loop do cursor entre START TRANSACTION...COMMIT.

Para configurar um cursor, primeiro declare uma variável de cursor. Todo o acesso a cursores na PL/pgSQL passa por variáveis de cursor, que sempre são do tipo de dados especial refcursor. Um tipo de dados refcursor apenas mantém uma referência a um cursor.

Você pode criar uma variável de cursor declarando-a como uma variável do tipo refcursor. Ou, use a sintaxe de declaração de cursor a seguir.

name CURSOR [ ( arguments ) ] FOR query ;

No trecho acima, arguments (se especificado) é uma lista separada por vírgulas de pares name datatype em que cada um define nomes a serem substituídos por valores de parâmetros em query. Os valores reais que substituirão esses nomes serão especificados posteriormente, quando o cursor for aberto.

Veja a seguir alguns exemplos.

DECLARE
  curs1 refcursor;
  curs2 CURSOR FOR SELECT * FROM tenk1;
  curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;

Essas três variáveis têm o tipo de dados refcursor, mas a primeira pode ser usada com qualquer consulta. Por outro lado, a segunda tem uma consulta totalmente especificada já associada a ela, e a última tem uma consulta parametrizada associada a ela. O valor key é substituído por um valor de parâmetro inteiro quando o cursor é aberto. Diz-se que a variável curs1 é não associada pois ela não está vinculada a nenhuma consulta específica.

Antes de usar um cursor para recuperar linhas, ele deve ser aberto. PL/pgSQL possui três formas da instrução OPEN, das quais duas usam variáveis de cursor não associadas e a terceira usa uma variável de cursor associada:

  • Abrir para seleção: a variável de cursor é aberta e recebe a consulta especificada para execução. O cursor não pode já estar aberto. Além disso, ele deverá ter sido declarado como um cursor não associado (isto é, como uma variável refcursor simples). A consulta SELECT é tratada da mesma maneira que outras instruções SELECT na PL/pgSQL. 

    OPEN cursor_name FOR SELECT ...;

    Por exemplo:

    OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

  • Abrir para execução: a variável de cursor é aberta e recebe a consulta especificada para execução. O cursor não pode já estar aberto. Além disso, ele deverá ter sido declarado como um cursor não associado (isto é, como uma variável refcursor simples). A consulta é especificada como uma expressão de string da mesma maneira que no comando EXECUTE. Essa abordagem oferece flexibilidade para que a consulta possa variar de uma execução para a próxima.

    OPEN cursor_name FOR EXECUTE query_string;

    Por exemplo:

    OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

  • Abrir um cursor associado: essa forma de OPEN é usada para abrir uma variável de cursor cuja consulta foi associada a ela no momento em que foi declarada. O cursor não pode já estar aberto. Uma lista das expressões de valor de argumento reais deverá aparecer se, e somente se, o cursor tiver sido declarado para receber argumentos. Esses valores são substituídos na consulta. 

    OPEN bound_cursor_name [ ( argument_values ) ];

    Por exemplo:

    OPEN curs2;
OPEN curs3(42);

Depois que um cursor tiver sido aberto, trabalhe com ele usando as instruções descritas a seguir. Essas instruções não precisam ocorrer no mesmo procedimento armazenado que abriu o cursor. Você pode retornar um valor refcursor fora de um procedimento armazenado e deixar o chamador operar no cursor. Todos os portais são implicitamente fechados ao final da transação. Portanto, você pode usar um valor refcursor para fazer referência a um cursor aberto somente até o final da transação.

  • FETCH recupera a próxima linha do cursor em um destino. Esse destino pode ser uma variável de linha, uma variável de registro ou uma lista separada por vírgulas de variáveis simples, igual à SELECT INTO. Como na SELECT INTO, é possível verificar a variável especial FOUND para conferir se uma linha foi obtida.

    FETCH cursor INTO target;

    Por exemplo:

    FETCH curs1 INTO rowvar;

  • CLOSE fecha o portal subjacente a um cursor aberto. Use essa instrução para liberar recursos antes do fim da transação. Você também pode usar essa instrução a fim de liberar a variável de cursor para que possa ser aberta novamente.

    CLOSE cursor;

    Por exemplo:

    CLOSE curs1;

RAISE

Use a instrução RAISE level para relatar mensagens e gerar erros.

RAISE level 'format' [, variable [, ...]];

Os níveis possíveis são NOTICE, INFO, LOG, WARNING e EXCEPTION. EXCEPTION gera um erro, que normalmente cancela a transação atual. Os outros níveis geram apenas mensagens com diferentes níveis de prioridade.

Dentro da string de formato, % é substituído pela representação de string do próximo argumento opcional. Escreva %% para emitir um % literal. No momento, argumentos opcionais devem ser variáveis simples, não expressões, e o formato deve ser uma string literal simples.

No exemplo a seguir, o valor de v_job_id substitui o % na string.

RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;

Use a instrução RAISE para relançar a exceção capturada por um bloco de tratamento de exceções. Essa instrução só é válida em blocos de tratamento de exceções de procedimentos armazenados no modo NONATOMIC.

RAISE;

Controle da transação

Você pode trabalhar com instruções de controle de transação na linguagem PL/pgSQL que o Amazon Redshift usa. Para obter informações sobre como usar as instruções COMMIT, ROLLBACK e TRUNCATE em um procedimento armazenado, consulte Gerenciamento de transações.

Nos procedimentos armazenados no modo NONATOMIC, use START TRANSACTION para iniciar um bloco de transação.

START TRANSACTION;
nota

A instrução PL/pgSQL START TRANSACTION é diferente do comando SQL START TRANSACTION das seguintes formas:

  • Nos procedimentos armazenados, START TRANSACTION não é sinônimo de BEGIN.

  • A instrução PL/pgSQL não é compatível com palavras-chave opcionais de nível de isolamento e permissão de acesso.