35.6. Instruções básicas

35.6.1. Atribuições

A atribuição de um valor a uma variável, ou a um campo de linha ou de registro, é escrita da seguinte maneira:

identificador := expressão;

Conforme explicado anteriormente, a expressão nesta instrução é avaliada através de um comando SELECT do SQL enviado para a máquina de banco de dados principal. A expressão deve produzir um único valor.

Se o tipo de dado do resultado da expressão não corresponder ao tipo de dado da variável, ou se a variável possuir um tipo/precisão específico (como char(20)), o valor do resultado será convertido implicitamente pelo interpretador do PL/pgSQL, utilizando a função de saída do tipo do resultado e a função de entrada do tipo da variável. Deve ser observado que este procedimento pode ocasionar erros em tempo de execução gerados pela função de entrada, se a forma cadeia de caracteres do valor do resultado não puder ser aceita pela função de entrada.

Exemplos:

id_usuario := 20;
taxa := subtotal * 0.06;

35.6.2. SELECT INTO

O resultado de um comando SELECT que retorna várias colunas (mas apenas uma linha) pode ser atribuído a uma variável registro, a uma variável tipo-linha, ou a uma lista de variáveis escalares. É feito através de

SELECT INTO destino expressões_de_seleção FROM ...;

onde destino pode ser uma variável registro, uma variável linha, ou uma lista separada por vírgulas de variáveis simples e campos de registro/linha. A expressões_de_seleção e o restante do comando são os mesmos que no SQL comum.

Deve ser observado que é bem diferente da interpretação normal de SELECT INTO feita pelo PostgreSQL, onde o destino de INTO é uma nova tabela criada. Se for desejado criar uma tabela dentro de uma função PL/pgSQL a partir do resultado do SELECT, deve ser utilizada a sintaxe CREATE TABLE ... AS SELECT.

Se for utilizado como destino uma linha ou uma lista de variáveis, os valores selecionados devem corresponder exatamente à estrutura do destino, senão ocorre um erro em tempo de execução. Quando o destino é uma variável registro, esta se autoconfigura automaticamente para o tipo linha das colunas do resultado da consulta.

Exceto pela cláusula INTO, a instrução SELECT é idêntica ao comando SELECT normal do SQL, podendo utilizar todos os seus recursos.

A cláusula INTO pode aparecer em praticamente todos os lugares na instrução SELECT. Habitualmente é escrita logo após o SELECT, conforme mostrado acima, ou logo antes do FROM — ou seja, logo antes ou logo após a lista de expressões_de_seleção.

Se a consulta não retornar nenhuma linha, são atribuídos valores nulos aos destinos. Se a consulta retornar várias linhas, a primeira linha é atribuída aos destinos e as demais são desprezadas; deve ser observado que "a primeira linha" não é bem definida a não ser que seja utilizado ORDER BY.

A variável especial FOUND pode ser verificada imediatamente após a instrução SELECT INTO para determinar se a atribuição foi bem-sucedida, ou seja, foi retornada pelo menos uma linha pela consulta. (consulte a Seção 35.6.6). Por exemplo:

SELECT INTO meu_registro * FROM emp WHERE nome_emp = meu_nome;
IF NOT FOUND THEN
    RAISE EXCEPTION ''não foi encontrado o empregado %!'', meu_nome;
END IF;

Para testar se o resultado do registro/linha é nulo, pode ser utilizada a condição IS NULL. Entretanto, não existe maneira de saber se foram desprezadas linhas adicionais. A seguir está mostrado um exemplo que trata o caso onde não foi retornada nenhuma linha:

DECLARE
    registro_usuario RECORD;
BEGIN
    SELECT INTO registro_usuario * FROM usuarios WHERE id_usuario=3;

    IF registro_usuario.pagina_web IS NULL THEN
        -- o usuario não informou a página na web, retornar "http://"
        RETURN ''http://'';
    END IF;
END;

35.6.3. Execução de expressão ou de consulta sem resultado

Algumas vezes se deseja avaliar uma expressão ou comando e desprezar o resultado (normalmente quando está sendo chamada uma função que produz efeitos colaterais, mas não possui nenhum valor de resultado útil). Para se fazer isto no PL/pgSQL é utilizada a instrução PERFORM:

PERFORM comando;

Esta instrução executa o comando e despreza o resultado. A instrução deve ser escrita da mesma maneira que se escreve um comando SELECT do SQL, mas com a palavra chave inicial SELECT substituída por PERFORM. As variáveis da linguagem PL/pgSQL são substituídas no comando da maneira usual. Além disso, a variável especial FOUND é definida como verdade se a instrução produzir pelo menos uma linha, ou falso se não produzir nenhuma linha.

Exemplo:

PERFORM create_mv('cs_session_page_requests_mv', my_query);

35.6.4. Não fazer nada

Algumas vezes uma instrução guardadora de lugar que não faz nada é útil. Por exemplo, pode indicar que uma ramificação da cadeia if/then/else está deliberadamente vazia. Para esta finalidade deve ser utilizada a instrução NULL:

NULL;

Por exemplo, os dois fragmentos de código a seguir são equivalentes:

    BEGIN
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN
            NULL;  -- ignorar o erro
    END;

    BEGIN
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN  -- ignorar o erro
    END;

Qual dos dois escolher é uma questão de gosto.

35.6.5. Execução de comandos dinâmicos

As vezes é necessário gerar comandos dinâmicos dentro da função PL/pgSQL, ou seja, comandos que envolvem tabelas diferentes ou tipos de dado diferentes cada vez que são executados. A tentativa normal do PL/pgSQL de colocar planos para os comandos no cache não funciona neste cenário. A instrução EXECUTE é fornecida para tratar este tipo de problema:

EXECUTE cadeia_de_caracteres_do_comando;

onde cadeia_de_caracteres_do_comando é uma expressão que produz uma cadeia de caracteres (do tipo text) contendo o comando a ser executado. A cadeia de caracteres é enviada literalmente para a máquina SQL.

Em particular, deve-se observar que não é feita a substituição das variáveis do PL/pgSQL na cadeia de caracteres do comando. Os valores das variáveis devem ser inseridos na cadeia de caracteres do comando quando esta é construída.

Diferentemente de todos os outros comandos do PL/pgSQL, o comando executado pela instrução EXECUTE não é preparado e salvo apenas uma vez por todo o tempo de duração da sessão. Em vez disso, o comando é preparado cada vez que a instrução é executada. A cadeia de caracteres do comando pode ser criada dinamicamente dentro da função para realizar ações em tabelas e colunas diferentes.

Os resultados dos comandos SELECT são desprezados pelo EXECUTE e, atualmente, o SELECT INTO não é suportado pelo EXECUTE. Portanto não há maneira de extrair o resultado de um comando SELECT criado dinamicamente utilizando o comando EXECUTE puro. Entretanto, há duas outras maneiras disto ser feito: uma é utilizando o laço FOR-IN-EXECUTE descrito na Seção 35.7.4, e a outra é utilizando um cursor com OPEN-FOR-EXECUTE, conforme descrito na Seção 35.8.2.

Quando se trabalha com comandos dinâmicos, muitas vezes é necessário tratar o escape dos apóstrofos. O método recomendado para delimitar texto fixo no corpo da função é utilizar o cifrão (Caso exista código legado que não utiliza a delimitação por cifrão por favor consulte a visão geral na Seção 35.2.1, que pode ajudar a reduzir o esforço para converter este código em um esquema mais razoável).

Os valores dinâmicos a serem inseridos nos comandos construídos requerem um tratamento especial, uma vez que estes também podem conter apóstrofos ou aspas. Um exemplo (assumindo que está sendo utilizada a delimitação por cifrão para a função como um todo e, portanto, os apóstrofos não precisam ser duplicados) é:

EXECUTE 'UPDATE tbl SET '
        || quote_ident(nome_da_coluna)
        || ' = '
        || quote_literal(novo_valor)
        || ' WHERE key = '
        || quote_literal(valor_chave);

Este exemplo mostra o uso das funções quote_ident(text) e quote_literal(text). Por motivo de segurança, as variáveis contendo identificadores de coluna e de tabela devem ser passadas para a função quote_ident. As variáveis contendo valores que devem se tornar literais cadeia de caracteres no comando construído devem ser passadas para função quote_literal. Estas duas funções executam os passos apropriados para retornar o texto de entrada envolto por aspas ou apóstrofos, respectivamente, com todos os caracteres especiais presentes devidamente colocados em seqüências de escape.

Deve ser observado que a delimitação por cifrão somente é útil para delimitar texto fixo. Seria uma péssima idéia tentar codificar o exemplo acima na forma

EXECUTE 'UPDATE tbl SET '
        || quote_ident(nome_da_coluna)
        || ' = $$'
        || novo_valor
        || '$$ WHERE key = '
        || quote_literal(valor_chave);

porque não funcionaria se o conteúdo de novo_valor tivesse $$. A mesma objeção se aplica a qualquer outra delimitação por cifrão escolhida. Portanto, para delimitar texto que não é previamente conhecido deve ser utilizada a função quote_literal.

Pode ser visto no Exemplo 35-8, onde é construído e executado um comando CREATE FUNCTION para definir uma nova função, um caso muito maior de comando dinâmico e EXECUTE.

35.6.6. Obtenção do status do resultado

Existem diversas maneiras de determinar o efeito de um comando. O primeiro método é utilizar o comando GET DIAGNOSTICS, que possui a forma:

GET DIAGNOSTICS variável = item [ , ... ] ;

Este comando permite obter os indicadores de status do sistema. Cada item é uma palavra chave que identifica o valor de estado a ser atribuído a variável especificada (que deve ser do tipo de dado correto para poder receber o valor). Os itens de status disponíveis atualmente são ROW_COUNT, o número de linhas processadas pelo último comando SQL enviado para a máquina SQL, e RESULT_OID, o OID da última linha inserida pelo comando SQL mais recente. Deve ser observado que RESULT_OID só tem utilidade após um comando INSERT.

Exemplo:

GET DIAGNOSTICS variável_inteira = ROW_COUNT;

O segundo método para determinar os efeitos de um comando é verificar a variável especial FOUND, que é do tipo boolean. A variável FOUND é iniciada como falso dentro de cada chamada de função PL/pgSQL. É definida por cada um dos seguintes tipos de instrução:

• A instrução SELECT INTO define FOUND como verdade quando retorna uma linha, e como falso quando não retorna nenhuma linha.

• A instrução PERFORM define FOUND como verdade quando produz (e despreza) uma linha, e como falso quando não produz nenhuma linha.

• As instruções UPDATE, INSERT e DELETE definem FOUND como verdade quando pelo menos uma linha é afetada, e como falso quando nenhuma linha é afetada.

• A instrução FETCH define FOUND como verdade quando retorna uma linha, e como falso quando não retorna nenhuma linha.

• A instrução FOR define FOUND como verdade quando interage uma ou mais vezes, senão define como falso. Isto se aplica a todas três variantes da instrução FOR (laços FOR inteiros, laços FOR em conjuntos de registros, e laços FOR em conjuntos de registros dinâmicos). A variável FOUND é definida desta maneira ao sair do laço FOR: dentro da execução do laço a variável FOUND não é modificada pela instrução FOR, embora possa ser modificada pela execução de outras instruções dentro do corpo do laço.

FOUND é uma variável local dentro de cada função PL/pgSQL; qualquer mudança feita na mesma afeta somente a função corrente.