A atribuição de um valor a uma variável do PL/pgSQL é escrita como:

variável { := | = } expressão;

Conforme explicado anteriormente, a expressão nesta instrução é avaliada por meio de um comando SELECT submetido ao mecanismo de banco de dados principal. A expressão deve produzir um único valor (possivelmente um valor de linha, se uma variável tipo-linha ou variável tipo-registro). A variável de destino pode ser uma variável simples (opcionalmente qualificada com um nome de bloco), um campo de uma linha ou variável de registro, um elemento ou parte de uma matriz. Pode ser usado igual (=) no lugar do := compatível com o PL/SQL do Oracle.

Se o tipo de dados do resultado da expressão não corresponder ao tipo de dados da variável, o valor será transformado como se fosse uma conversão de atribuição (veja Armazenamento de valor). Se nenhuma conversão de atribuição for conhecida para o par de tipos de dados envolvidos, o interpretador do PL/pgSQL tentará converter o valor do resultado para a forma textual, ou seja, aplicando a função de saída do tipo de dados do resultado, seguida pela função de entrada do tipo de dados da variável. Note que este procedimento pode resultar em erros de tempo de execução gerados pela função de entrada do tipo de dados, se a forma cadeia de caracteres do valor do resultado não for aceita por esta função de entrada.

Exemplos:

imposto := subtotal * 0.06;
meu_registro.id_usuario := 20;
minha_matriz[j] := 20;
minha_matriz[1:3] := array[1,2,3];
matriz_complexa[n].realpart = 12.3;

De uma maneira geral, qualquer comando SQL que não retorna linhas pode ser executado no corpo de uma função PL/pgSQL, apenas escrevendo o comando. Por exemplo, pode ser criada e preenchida uma tabela escrevendo

CREATE TABLE minha_tabela (id int primary key, dados text);
INSERT INTO minha_tabela VALUES (1,'um'), (2,'dois');

Se o comando retornar linhas (por exemplo, SELECT, ou INSERT/UPDATE/DELETE/MERGE com RETURNING), há duas maneiras de proceder: Quando o comando retorna no máximo uma linha, ou estando preocupado apenas com a primeira linha da saída, o comando deve ser escrito como de costume, mas adicionada a cláusula INTO para capturar a saída, conforme descrito em Execução de comando com uma única linha de resultado; Para processar todas as linhas da saída, o comando deve ser escrito como fonte de dados para um laço FOR, conforme descrito em Percorrendo os resultados da consulta.

Normalmente, não basta apenas executar comandos SQL definidos estaticamente. Tipicamente, se deseja que o comando use valores de dados variáveis, ou até mesmo varie de maneiras mais fundamentais, como usar nomes de tabela diferentes em momentos diferentes. Novamente, existem duas maneiras de proceder, dependendo da situação.

Os valores das variáveis do PL/pgSQL podem ser inseridos automaticamente nos comandos SQL otimizáveis, que são SELECT, INSERT, UPDATE, DELETE, MERGE e alguns comandos utilitários que incorporam um desses comandos, como EXPLAIN e CREATE TABLE ... AS SELECT. Nesses comandos, qualquer nome de variável do PL/pgSQL que apareça no texto do comando é substituído por um parâmetro de consulta e, em seguida, o valor corrente da variável é fornecido como valor do parâmetro em tempo de execução, exatamente como foi descrito anteriormente para o processamento de expressões; para obter detalhes veja Substituição de variável.

Ao executar o comando SQL otimizável dessa maneira, o PL/pgSQL pode armazenar em cache e reutilizar o plano de execução do comando, conforme discutido em Cache de plano.

Comandos SQL não otimizáveis (também chamados de comandos utilitários) não podem aceitar parâmetros de consulta. Portanto, a substituição automática de variáveis do PL/pgSQL não funciona nesses comandos. Para incluir texto variável em um comando utilitário executado a partir do PL/pgSQL, o comando utilitário deve ser criado como uma cadeia de caracteres e, em seguida, usado o EXECUTE conforme discutido em Execução de comandos dinâmicos.

A instrução EXECUTE também deve ser usada se for desejado modificar o comando de alguma outra forma, além de fornecer valor para os dados, por exemplo, para alterar o nome de uma tabela.

Às vezes é útil executar uma expressão ou consulta SELECT, mas descartar o resultado, por exemplo, ao chamar uma função com efeitos colaterais, mas sem nenhum valor de resultado útil. Para fazer isto no PL/pgSQL, é usada a instrução PERFORM:

PERFORM consulta;

Esta instrução executa a consulta e descarta o resultado. A consulta deve ser escrita da mesma forma como se escreveria um comando SELECT, mas substituindo a palavra-chave inicial SELECT por PERFORM. Para consultas com WITH, o PERFORM deve ser usado com a consulta entre parênteses. (Neste caso, a consulta pode retornar apenas uma linha.) As variáveis do PL/pgSQL são substituídas na consulta conforme descrito acima, e o plano é armazenado em cache da mesma maneira. Além disso, a variável especial FOUND é definida como verdade se a consulta produzir pelo menos uma linha, ou falso se não produzir nenhuma linha (veja Obtenção do status do resultado).

Exemplo:

PERFORM create_mv('cs_session_page_requests_mv', my_query);

O resultado de um comando SQL que produz uma única linha (possivelmente com várias colunas), pode ser atribuído a uma variável tipo-registro, tipo-linha, ou a uma lista de variáveis escalares. Isto é feito escrevendo o comando SQL base e adicionando a cláusula INTO. Por exemplo,

SELECT expressões_select INTO [STRICT] destino FROM ...;
INSERT ... RETURNING expressões INTO [STRICT] destino;
UPDATE ... RETURNING expressões INTO [STRICT] destino;
DELETE ... RETURNING expressões INTO [STRICT] destino;
MERGE ... RETURNING expressões INTO [STRICT] destino;

onde destino pode ser uma variável de registro, uma variável tipo-linha, ou uma lista separada por vírgulas de variáveis simples e campos de registro/linha. As variáveis do PL/pgSQL são substituídas no restante do comando (ou seja, tudo menos a cláusula INTO) conforme descrito acima, e o plano será armazenado em cache da mesma maneira. Isto funciona para SELECT, INSERT/UPDATE/DELETE/MERGE com RETURNING, e certos comandos utilitários que retornam conjuntos de linhas, como EXPLAIN. Com exceção da cláusula INTO, o comando SQL é idêntico ao que seria escrito fora do PL/pgSQL.

Se for usada uma variável tipo-linha ou uma lista de variáveis como destino, as colunas de resultado do comando devem corresponder exatamente à estrutura do destino quanto ao número e tipos dos dados, caso contrário, ocorrerá um erro de tempo de execução. Quando uma variável tipo-registro é o destino, esta variável se configura automaticamente para o tipo de linha das colunas de resultado do comando.

A cláusula INTO pode aparecer em quase qualquer lugar no comando SQL. Habitualmente é escrita logo antes ou após a lista de expressões_select em um comando SELECT, ou no final do comando para outros tipos de comando. É recomendado seguir esta convenção, para o caso do analisador do PL/pgSQL se tornar mais exigente em futuras versões.

Se não for especificado STRICT na cláusula INTO, então destino recebe a primeira linha retornada pelo comando, ou nulos se o comando não retornar nenhuma linha. (Note que a “primeira linha” não fica bem definida, a menos que se use ORDER BY.) Todas as linhas do resultado após a primeira linha são descartadas. Pode ser verificada a variável especial FOUND (veja Obtenção do status do resultado) para determinar se alguma linha foi retornada:

SELECT * INTO emp_reg FROM emp WHERE emp_nome = nome;
IF NOT FOUND THEN
    RAISE EXCEPTION 'empregado % não encontrado', nome;
END IF;

Se for especificada a opção STRICT, o comando deve retornar exatamente uma linha, ou será relatado um erro de tempo de execução: NO_DATA_FOUND (nenhuma linha); ou TOO_MANY_ROWS (mais de uma linha). Pode ser usado um bloco de exceção se for desejado capturar o erro. Por exemplo:

BEGIN
    SELECT * INTO STRICT emp_reg FROM emp WHERE emp_nome = nome;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'empregado % não encontrado', nome;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'empregado % não é único', nome;
END;

A execução bem-sucedida do comando com STRICT sempre define FOUND como verdade.

Para INSERT/UPDATE/DELETE/MERGE com RETURNING, o PL/pgSQL relata erro para mais de uma linha retornada, mesmo quando STRICT não é especificado. Isto ocorre por não haver uma opção como ORDER BY para determinar qual linha afetada deve ser retornada.

Se print_strict_params estiver ativo para a função, quando um erro for lançado porque os requisitos de STRICT não foram atendidos, a parte DETAIL da mensagem de erro incluirá informações sobre os parâmetros passados para o comando. Pode ser alterada a configuração de print_strict_params para todas as funções, definindo plpgsql.print_strict_params, embora apenas as compilações de função subsequentes sejam afetadas. Também pode ser ativada para cada função, usando a opção do compilador. Por exemplo:

CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#print_strict_params on
DECLARE
userid int;
BEGIN
    SELECT users.userid INTO STRICT userid
        FROM users WHERE users.username = get_userid.username;
    RETURN userid;
END;
$$ LANGUAGE plpgsql;

Em caso de falha, esta função pode produzir uma mensagem de erro como:

SELECT get_userid('Ana');

ERRO:  consulta não retornou registros
DETALHE:  parameters: username = 'Ana'
CONTEXTO:  função PL/pgSQL get_userid(text) linha 6 em comando SQL

Muitas vezes se deseja gerar comandos dinâmicos dentro de funções PL/pgSQL, ou seja, comandos que envolvem tabelas diferentes, ou tipos de dados diferentes, cada vez que são executados. As tentativas normais do PL/pgSQL de armazenar planos para os comandos em cache (conforme discutido em Cache de plano) não funcionam nesses cenários. Para lidar com este tipo de problema, é fornecida a instrução EXECUTE:

EXECUTE cadeia_de_caracteres_de_comando [ INTO [STRICT] destino ] [ USING expressão [, ... ] ];

onde cadeia_de_caracteres_do_comando é uma expressão que gera uma cadeia de caracteres (do tipo text) contendo o comando a ser executado. O destino opcional é uma variável de registro, uma variável tipo-linha, ou uma lista separada por vírgulas de variáveis simples e campos de registro/linha, onde os resultados do comando são armazenados. As expressões USING opcionais fornecem valores a serem inseridos no comando.

Nenhuma substituição de variáveis do PL/pgSQL é feita na cadeia de caracteres de comando computada. Quaisquer valores de variável requeridos devem ser inseridos na cadeia de caracteres do comando à medida que mesma é construída; ou podem ser usados parâmetros conforme descrito abaixo.

Além disso, não existe cache de plano para comandos executados via EXECUTE. Em vez disso, o comando é sempre planejado toda vez que a instrução é executada. Assim, a cadeia de caracteres de comando pode ser criada dinamicamente dentro da função para executar ações em diferentes tabelas e colunas.

A cláusula INTO especifica onde devem ser clocados os resultados do comando SQL retornando linhas. Se for fornecida uma variável tipo-linha ou lista de variáveis, ela deverá corresponder exatamente à estrutura dos resultados do comando; se for fornecida uma variável de registro, ela se configurará para corresponder à estrutura do resultado automaticamente. Se forem retornadas várias linhas, apenas a primeira será atribuída à(s) variável(is) da cláusula INTO. Se não for retornada nenhuma linha, será atribuído NULL à(s) variável(is) da cláusula INTO. Se não for especificada nenhuma cláusula INTO, os resultados do comando serão descartados.

Se a opção STRICT for fornecida, será relatado um erro, a menos que o comando retorne exatamente uma linha.

A cadeia de caracteres de comando pode usar valores de parâmetros, referenciados no comando como $1, $2, etc. Estes símbolos fazem referência a valores fornecidos na cláusula USING. Este método é geralmente preferível a inserir os valores dos dados na cadeia de caracteres de comando como texto: evita a sobrecarga de tempo de execução da conversão dos valores em texto e vice-versa, sendo muito menos propenso a ataques de injeção de SQL, por não haver necessidade de delimitadores ou escapes. Por exemplo:

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Note que os símbolos de parâmetro só podem ser usados para valores de dados — se for desejado usar nomes de tabela ou de coluna especificados dinamicamente, eles deverão ser inseridos textualmente na cadeia de caracteres do comando. Por exemplo, se a consulta anterior precisasse ser feita em uma tabela selecionada dinamicamente, poderia ser escrito o seguinte:

EXECUTE 'SELECT count(*) FROM '
    || quote_ident(tabname)
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Uma abordagem mais limpa é usar a especificação %I da função format() para inserir nomes de tabelas ou de colunas com delimitação automática:

EXECUTE format('SELECT count(*) FROM %I '
   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
   INTO c
   USING checked_user, checked_date;

(Este exemplo conta com a regra do SQL de que literais cadeia de caracteres separados por nova linha são implicitamente concatenados.)

Outra restrição quanto aos símbolos de parâmetro, é funcionarem apenas nos comandos SQL otimizáveis (SELECT, INSERT, UPDATE, DELETE, MERGE e certos comandos contendo um deles). Em outros tipos de instrução (chamadas genericamente de instruções utilitárias), os valores devem ser inseridos textualmente, mesmo que sejam apenas valores de dados.

Uma instrução EXECUTE contendo uma cadeia de caracteres de comando constante simples e alguns parâmetros USING, como no primeiro exemplo acima, é funcionalmente equivalente a apenas escrever o comando diretamente no PL/pgSQL, e permitir que a substituição das variáveis do PL/pgSQL ocorra automaticamente. A diferença importante é que a instrução EXECUTE replaneja o comando a cada execução, gerando um plano específico para os valores correntes dos parâmetros; enquanto o PL/pgSQL pode criar um plano genérico e armazená-lo em cache para ser usado novamente. Em situações onde o melhor plano depende fortemente dos valores dos parâmetros, pode ser útil usar EXECUTE para ter certeza que não será selecionado um plano genérico.

SELECT INTO não tem suporte no momento no EXECUTE; em vez disso, deve ser executado um comando SELECT simples e especificado INTO como parte do próprio EXECUTE.

EXECUTE format('UPDATE tbl SET %I = $1 '
   'WHERE key = $2', colname) USING newvalue, keyvalue;

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

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

'WHERE key = ' || quote_nullable(keyvalue)

'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)

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

EXECUTE format('UPDATE tbl SET %I = %L '
   'WHERE key = %L', colname, newvalue, keyvalue);

EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
   USING newvalue, keyvalue;

-- Equivalente a SET SERVEROUTPUT ON no Oracle
SET CLIENT_MIN_MESSAGES = 'debug';
DO $$
DECLARE
tabela text := 'minha tabela';
coluna text := 'minha coluna';
nome   text := 'Maria D''Almeida';
chave  text := 'chave especial';
BEGIN
RAISE DEBUG USING MESSAGE :=
    'UPDATE '::text
    || quote_ident(tabela)
    || ' SET '::text
    || quote_ident(coluna)
    || ' = '::text
    || quote_literal(nome)
    || ' WHERE chave = '::text
    || quote_literal(chave);
END $$;
SET
DEPURAÇÃO:  UPDATE "minha tabela" ↵
            SET "minha coluna" = 'Maria D''Almeida' ↵
            WHERE chave = 'chave especial'
DO

Um exemplo muito maior de um comando dinâmico e EXECUTE pode ser visto no Exemplo 41.12, que constrói e executa um comando CREATE FUNCTION para definir uma nova função.

Existem várias maneiras de determinar o efeito de um comando. O primeiro método é usar o comando GET DIAGNOSTICS, que tem o formato:

GET [CURRENT] DIAGNOSTICS variável { = | := } item [ , ... ];

Este comando permite a recuperação de indicadores de status do sistema. CURRENT é apenas uma palavra ruído opcional (mas veja também GET STACKED DIAGNOSTICS em Obtenção de informações sobre o erro). Cada item é uma palavra-chave que identifica o valor de status a ser atribuído à variável especificada (que deve ser do tipo de dados correto para recebê-lo). Os itens de status atualmente disponíveis estão mostrados na Tabela 41.1. Pode ser usado dois pontos seguido de igual (:=), em vez do sinal = do padrão SQL, na expressão. Por exemplo:

GET DIAGNOSTICS variável_inteira = ROW_COUNT;

O segundo método para determinar os efeitos de um comando é verificar a variável especial chamada FOUND, do tipo de dados boolean. FOUND começa com falso dentro de cada chamada de função PL/pgSQL, sendo definido depois por cada um dos seguintes tipos de declarações:

As demais instruções do PL/pgSQL não alteram o estado de FOUND. Note, em particular, que EXECUTE altera a saída de GET DIAGNOSTICS, mas não altera FOUND.

FOUND é uma variável local dentro de cada função PL/pgSQL; qualquer alteração afeta apenas a função corrente.

Às vezes, uma instrução de marcador de espaço que não faz nada é útil. Por exemplo, pode indicar que o ramo de uma cadeia if/then/else está deliberadamente vazio. Para isto, é usada a instrução NULL:

NULL;

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

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

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

Qual preferir é uma questão de gosto.