Uma boa maneira de desenvolver em PL/pgSQL
é usar o editor de texto de sua preferência para criar as funções
e, em outra janela, usar o psql para
carregar e testar estas funções.
Se estiver sendo feito dessa maneira, é uma boa ideia escrever a
função usando CREATE OR REPLACE FUNCTION.
Dessa forma, pode-se apenas recarregar o arquivo para
atualizar a definição da função. Por exemplo:
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
....
$$ LANGUAGE plpgsql;
No psql, o arquivo de definição da função pode ser carregado, ou recarregado, usando
\i nome_do_arquivo.sql
e executados os comandos SQL para testar a função logo a seguir.
Outra boa maneira de desenvolver em PL/pgSQL é usar uma ferramenta com interface gráfica (GUI) e acesso a banco de dados, e que facilite o desenvolvimento em linguagem procedural. Um exemplo desse tipo de ferramenta é o pgAdmin, mas existem outras. Estas ferramentas geralmente fornecem recursos úteis, como escape de apóstrofos e facilidades de recriação e depuração de funções.
O código de uma função PL/pgSQL é
especificado em CREATE FUNCTION como um literal
cadeia de caracteres.
Se o literal cadeia de caracteres for escrito da maneira comum,
com apóstrofos, todos os apóstrofos dentro do corpo da função devem
ser duplicados; da mesma forma, qualquer contrabarra deve ser duplicada
(assumindo que esteja sendo usada a sintaxe de escape na cadeia de
caracteres).
Duplicar apóstrofos é, no mínimo, aborrecido e, em casos mais
complicados, o código pode se tornar absolutamente incompreensível,
porque se pode facilmente precisar de meia dúzia ou mais de
apóstrofos adjacentes.
É recomendável que o corpo da função seja escrito como um literal
cadeia de caracteres “delimitado por cifrão”
(veja Constantes do tipo cadeia de caracteres delimitadas por cifrão).
Na abordagem de delimitação por cifrão não há necessidade de duplicar
apóstrofos, mas, por outro lado, deve-se tomar cuidado para escolher
um delimitador de cifrão diferente para cada nível de aninhamento
necessário.
Por exemplo, o comando CREATE FUNCTION
pode ser escrito como:
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
....
$PROC$ LANGUAGE plpgsql;
Dentro desse literal podem ser usados apóstrofos para delimitar
literais cadeia de caracteres em comandos
SQL, e $$ para delimitar
fragmentos de comandos SQL que se esteja
montando como cadeias de caracteres.
Se for necessário delimitar um texto que inclua $$,
pode ser usado $Q$, e assim por diante.
O quadro abaixo mostra o que deve ser feito para escrever o corpo da função entre apóstrofos (sem uso da delimitação por cifrão). Pode ser útil para tornar códigos anteriores à delimitação por cifrão mais fácil de serem compreendidos.
Para iniciar e terminar o corpo da função. Por exemplo:
CREATE FUNCTION foo() RETURNS integer AS '
....
' LANGUAGE plpgsql;
Em todos os lugares dentro do corpo da função entre apóstrofos, os apóstrofos devem aparecer em pares.
Para literais cadeia de caracteres dentro do corpo da função. Por exemplo:
a_output := ''Blah''; SELECT * FROM users WHERE f_name=''foobar'';
Na abordagem de delimitação por cifrão, seria escrito apenas:
a_output := 'Blah'; SELECT * FROM users WHERE f_name='foobar';
que é exatamente o que o analisador do PL/pgSQL veria nos dois casos.
Quando é necessário colocar apóstrofos em torno de uma constante cadeia de caracteres dentro do corpo da função. Por exemplo:
a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
O valor realmente anexado a a_output seria:
AND name LIKE 'foobar' AND xyz.
Na abordagem de delimitação por cifrão, seria escrito:
a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
tomando cuidado para que nenhuma delimitação por cifrão
em torno seja apenas $$.
Quando o apóstrofo em uma cadeia de caracteres dentro do corpo da função é adjacente ao final dessa constante cadeia de caracteres. Por exemplo:
a_output := a_output || '' AND name LIKE ''''foobar''''''
O valor anexado a a_output seria então:
AND name LIKE 'foobar'.
Na abordagem de delimitação por cifrão, seria escrito:
a_output := a_output || $$ AND name LIKE 'foobar'$$
Quando se deseja dois apóstrofos em uma constante cadeia de caracteres (representando 8 aspas), e isto é adjacente ao final dessa constante cadeia de caracteres (mais 2). Provavelmente só seria necessário ao escrever uma função que gera outras funções, como em Exemplo 41.12. Por exemplo:
a_output := a_output || '' if v_'' ||
referrer_keys.kind || '' like ''''''''''
|| referrer_keys.key_string || ''''''''''
then return '''''' || referrer_keys.referrer_type
|| ''''''; end if;'';
O valor de a_output seria então:
if v_... like ''...'' then return ''...''; end if;
Na abordagem de delimitação por cifrão, seria escrito:
a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
|| referrer_keys.key_string || $$'
then return '$$ || referrer_keys.referrer_type
|| $$'; end if;$$;
onde se assume que só é necessário usar apóstrofos únicos em
a_output, porque será recolocado entre
apóstrofos antes de ser usado.
Para ajudar o usuário a encontrar problemas simples,
mas comuns, antes que se tornem problemas reais, o
PL/pgSQL fornece
verificações adicionais.
Quando ativados, dependendo da configuração, podem ser usadas
para emitir uma advertência ( WARNING), ou um
erro (ERROR), durante a compilação da função.
Uma função que recebeu WARNING pode ser executada
sem produzir mais mensagens, portanto, é recomendável testá-la em um
ambiente de desenvolvimento separado.
Definir plpgsql.extra_warnings, ou
plpgsql.extra_errors, conforme o caso, para
"all" é encorajado em ambientes de
desenvolvimento e/ou teste.
Estas verificações adicionais são ativadas por meio das variáveis
de configuração plpgsql.extra_warnings para
advertências, e plpgsql.extra_errors para erros.
As duas podem ser definidas com uma lista separada por vírgulas
de verificações, "none" ou "all".
O padrão é "none".
No momento a lista de verificações possíveis inclui:
shadowed_variables #Verifica se a declaração oculta alguma variável definida anteriormente.
strict_multi_assignment #
Alguns comandos do PL/PgSQL permitem
atribuir valores a mais de uma variável por vez, como o
SELECT INTO.
Normalmente, o número de variáveis de destino e o número de
variáveis de origem devem corresponder, embora o
PL/PgSQL use NULL
para valores ausentes, e as variáveis extras são ignoradas.
Ativar esta verificação fará com que o
PL/PgSQL emita um
AVISO ou ERRO sempre
que o número de variáveis de destino e o número de variáveis
de origem forem diferentes.
too_many_rows #
Ativar esta verificação fará com que o
PL/PgSQL verifique se uma determinada
consulta retorna mais de uma linha quando a cláusula
INTO é usada.
Como a instrução INTO usa apenas uma linha,
ter uma consulta retornando várias linhas geralmente é
ineficiente e/ou não determinístico, portanto, está
provavelmente errado.
O exemplo a seguir mostra o efeito de
plpgsql.extra_warnings
definido como shadowed_variables:
SET plpgsql.extra_warnings TO 'shadowed_variables';
CREATE FUNCTION foo(f1 int) RETURNS int AS $$
DECLARE
f1 int;
BEGIN
RETURN f1;
END;
$$ LANGUAGE plpgsql;
AVISO: variável "f1" esconde uma variável previamente definida
LINHA 3: f1 int;
^
CREATE FUNCTION
O exemplo a seguir mostra os efeitos de configurar
plpgsql.extra_warnings como
strict_multi_assignment:
SET plpgsql.extra_warnings TO 'strict_multi_assignment'; CREATE OR REPLACE FUNCTION public.foo() RETURNS void LANGUAGE plpgsql AS $$ DECLARE x int; y int; BEGIN SELECT 1 INTO x, y; SELECT 1, 2 INTO x, y; SELECT 1, 2, 3 INTO x, y; END; $$; SELECT foo();
AVISO: número de campos de fonte e alvo na atribuição não correspondem
DETALHE: verificação strict_multi_assignment de extra_warnings está ativa.
DICA: Certifique-se que se a consulta retorna a lista exata de colunas.
AVISO: número de campos de fonte e alvo na atribuição não correspondem
DETALHE: verificação strict_multi_assignment de extra_warnings está ativa.
DICA: Certifique-se que se a consulta retorna a lista exata de colunas.
foo
-----
(1 linha)