Capítulo 35. PL/pgSQL - Linguagem procedural SQL

PostgreSQL 14.5: PL/pgSQL — Linguagem procedural SQL

Sumário
35.1. Visão geral
35.1.1. Vantagens da utilização da linguagem PL/pgSQL
35.1.2. Tipos de dado suportados nos argumentos e no resultado
35.2. Dicas para desenvolvimento em PL/pgSQL
35.3. Estrutura da linguagem PL/pgSQL
35.4. Declarações
35.4.1. Aliases para parâmetros de função
35.4.2. Cópia de tipo
35.4.3. Tipos linha
35.4.4. Tipos registro
35.4.5. RENAME
35.5. Expressões
35.6. Instruções básicas
35.6.1. Atribuições
35.6.2. SELECT INTO
35.6.3. Execução de expressão ou de consulta sem resultado
35.6.4. Não fazer nada
35.6.5. Execução de comandos dinâmicos
35.6.6. Obtenção do status do resultado
35.7. Estruturas de controle
35.7.1. Retorno de uma função
35.7.2. Condicionais
35.7.3. Laços simples
35.7.4. Laço através do resultado da consulta
35.7.5. Captura de erros
35.8. Cursores
35.8.1. Declaração de variável cursor
35.8.2. Abertura de cursor
35.8.3. Utilização de cursores
35.9. Erros e mensagens
35.10. Gatilhos escritos em PL/pgSQL
35.11. Conversão do PL/SQL do Oracle para o PL/pgSQL do PostgreSQL
35.11.1. Exemplos de conversão
35.11.2. Outros detalhes a serem observados
35.11.3. Apêndice
35.12. Conversão do Transact-SQL do SQL Server para o PL/pgSQL do PostgreSQL

PL/pgSQL é uma linguagem procedural carregável desenvolvida para o sistema de banco de dados PostgreSQL. Os objetivos de projeto da linguagem PL/pgSQL foram no sentido de criar uma linguagem procedural carregável que pudesse:

Exceto pelas funções de conversão de entrada/saída e cálculos para os tipos definidos pelo usuário, tudo mais que pode ser definido por uma função escrita na linguagem C também pode ser feito usando PL/pgSQL. Por exemplo, é possível criar funções para cálculos condicionais complexos e depois usá-las para definir operadores ou em expressões de índice.

35.1. Visão geral

O tratador de chamadas da linguagem PL/pgSQL analisa o texto do código fonte da função e produz uma árvore de instruções binária interna, na primeira vez em que a função é chamada (em cada sessão). A árvore de instruções traduz inteiramente a estrutura da declaração PL/pgSQL, mas as expressões SQL individuais e os comandos SQL utilizados na função não são traduzidos imediatamente.

Assim que cada expressão ou comando SQL é utilizado pela primeira vez na função, o interpretador do PL/pgSQL cria um plano de execução preparado (utilizando as funções SPI_prepare e SPI_saveplan do gerenciador da Interface de Programação do Servidor - SPI). As execuções posteriores da expressão ou do comando reutilizam o plano preparado. Por isso, uma função com código condicional, contendo muitas declarações que podem requerer um plano de execução, somente prepara e salva os planos realmente utilizados durante o espaço de tempo da conexão com o banco de dados. Isto pode reduzir muito a quantidade total de tempo necessário para analisar e gerar os planos de execução para as declarações na função PL/pgSQL. A desvantagem é que erros em uma determinada expressão ou comando podem não ser detectados até que a parte da função onde se encontram seja executada.

Uma vez que o PL/pgSQL tenha construído um plano de execução para um determinado comando da função, este plano será reutilizado enquanto durar a conexão com o banco de dados. Normalmente há um ganho de desempenho, mas pode causar problema se o esquema do banco de dados for modificado dinamicamente. Por exemplo:

CREATE FUNCTION populate() RETURNS integer AS $$
DECLARE
    -- declarações
BEGIN
    PERFORM minha_funcao();
END;
$$ LANGUAGE plpgsql;

Se a função acima for executada fará referência ao OID da minha_funcao() no plano de execução gerado para a instrução PERFORM. Mais tarde, se a função minha_funcao() for removida e recriada, então populate() não vai mais conseguir encontrar minha_funcao(). Por isso é necessário recriar populate(), ou pelo menos começar uma nova sessão de banco de dados para que a função seja compilada novamente. Outra forma de evitar este problema é utilizar CREATE OR REPLACE FUNCTION ao atualizar a definição de minha_funcao (quando a função é "substituída" o OID não muda).

Uma vez que o PL/pgSQL salva os planos de execução desta maneira, os comandos SQL que aparecem diretamente na função PL/pgSQL devem fazer referência às mesmas tabelas e colunas em todas as execuções; ou seja, não pode ser utilizado um parâmetro como nome de tabela ou de coluna no comando SQL. Para contornar esta restrição podem ser construídos comandos dinâmicos utilizando a instrução EXECUTE do PL/pgSQL — o preço a ser pago é a construção de um novo plano de execução a cada execução.

Nota: A instrução EXECUTE do PL/pgSQL não tem relação com a instrução EXECUTE do SQL suportada pelo servidor PostgreSQL. A instrução EXECUTE do servidor não pode ser utilizada dentro das funções PL/pgSQL (e não é necessário).

35.1.1. Vantagens da utilização da linguagem PL/pgSQL

A linguagem SQL é a que o PostgreSQL (e a maioria dos bancos de dados relacionais) utiliza como linguagem de comandos. É portável e fácil de ser aprendida. Entretanto, todas as declarações SQL devem ser executadas individualmente pelo servidor de banco de dados.

Isto significa que o aplicativo cliente deve enviar o comando para o servidor de banco de dados, aguardar que seja processado, receber os resultados, realizar algum processamento, e enviar o próximo comando para o servidor. Tudo isto envolve comunicação entre processos e pode, também, envolver tráfego na rede se o cliente não estiver na mesma máquina onde se encontra o servidor de banco de dados.

Usando a linguagem PL/pgSQL pode ser agrupado um bloco de processamento e uma série de comandos dentro do servidor de banco de dados, juntando o poder da linguagem procedural com a facilidade de uso da linguagem SQL, e economizando muito tempo, porque não há necessidade da sobrecarga de comunicação entre o cliente e o servidor. Isto pode aumentar o desempenho consideravelmente.

Também podem ser utilizados na linguagem PL/pgSQL todos os tipos de dados, operadores e funções da linguagem SQL.

35.1.2. Tipos de dado suportados nos argumentos e no resultado

As funções escritas em PL/pgSQL aceitam como argumento qualquer tipo de dado escalar ou matriz suportado pelo servidor, e podem retornar como resultado qualquer um destes tipos. As funções também aceitam e retornam qualquer tipo composto (tipo linha) especificado por nome. Também é possível declarar uma função PL/pgSQL como retornando record, significando que o resultado é um tipo linha, cujas colunas são determinadas pela especificação no comando que faz a chamada, conforme mostrado na Seção 7.2.1.4.

As funções PL/pgSQL também podem ser declaradas como recebendo ou retornando os tipos polimórficos anyelement e anyarray. Os tipos de dado verdadeiros tratados pelas funções polimórficas podem variar entre chamadas, conforme mostrado na Seção 31.2.5. Na Seção 35.4.1 é mostrado um exemplo.

As funções PL/pgSQL também podem ser declaradas como retornando "set" (conjunto), ou tabela, de qualquer tipo de dado para o qual pode ser retornada uma única instância. Este tipo de função gera sua saída executando RETURN NEXT para cada elemento desejado do conjunto de resultados.

Por fim, uma função PL/pgSQL pode ser declarada como retornando void se não produzir nenhum valor de retorno útil.

Atualmente a linguagem PL/pgSQL não possui suporte total para os tipos domínio: trata o domínio da mesma maneira que o tipo escalar subjacente. Isto significa que não obriga respeitar as restrições associadas ao domínio, o que não representa problema para os argumentos da função, mas é perigoso declarar uma função PL/pgSQL como retornando um tipo domínio.

SourceForge.net Logo CSS válido!