37.6. Instruções básicas

37.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;

37.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.

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.

Atualmente a cláusula INTO pode estar praticamente em qualquer posição da instrução SELECT, mas se recomenda que seja colocada logo após a palavra chave SELECT, conforme mostrado acima. As próximas versões do PL/pgSQL podem ser mais rigorosas com relação à posição da cláusula INTO.

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. 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;
    nome_completo    varchar;
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;

37.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, que deve ser uma instrução SELECT, e despreza o resultado. As variáveis do PL/pgSQL são substituídas no comando da maneira habitual. Além disso, a variável especial FOUND é definida como verdade se a consulta produzir pelo menos uma linha, ou como falso se não produzir nenhuma linha.

Exemplo:

PERFORM create_mv(''cs_session_page_requests_mv'', my_query);

37.6.4. Execução de comandos dinâmicos

As vezes há necessidade de 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.

Quando se trabalha com comandos dinâmicos devem ser feitas seqüências de escape para os apóstrofos no PL/pgSQL. Por favor consulte a visão geral na Seção 37.2.1 , o que pode evitar trabalho.

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. Existem duas maneiras de extrair resultados de comandos SELECT criados dinamicamente: uma é utilizando a forma FOR-IN-EXECUTE descrita na Seção 37.7.4 , e a outra é utilizando um cursor com OPEN-FOR-EXECUTE, conforme descrito na Seção 37.8.2 .

Exemplo:

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

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.

A seguir está mostrado um exemplo maior de comandos dinâmicos e do EXECUTE:

CREATE FUNCTION cs_update_referrer_type_proc() RETURNS integer AS '
DECLARE
    referrer_keys RECORD;  -- declarar um registro genérico para ser utilizado no FOR
    a_output varchar(4000);
BEGIN
    a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar, varchar, varchar)
                  RETURNS varchar AS ''''
                     DECLARE
                         v_host   ALIAS FOR $1;
                         v_domain ALIAS FOR $2;
                         v_url    ALIAS FOR $3;
                     BEGIN '';

    -- Observe como é feita a varredura dos resultados da consulta
    -- no laço FOR utilizando a construção FOR <registro>.

    FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
        a_output := a_output
                 || '' IF v_'' || referrer_keys.kind || '' LIKE ''''''''''
                 || referrer_keys.key_string || '''''''''' THEN RETURN ''''''
                 || referrer_keys.referrer_type || ''''''; END IF;'';
    END LOOP;

    a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE plpgsql;'';

    EXECUTE a_output;
END;
' LANGUAGE plpgsql;

37.6.5. 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 variavel_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.