7.1.6. Execução de Comandos SQL
Os comandos SQL podem ser utilizados como instruções válidas dentro de funções PL/pgSQL. Esses comandos podem utilizar nomes de variáveis definidas na própria função para compor o comando.
Para ilustrar a execução de comandos, vamos criar uma tabela chamada pluviometros:
CREATE TABLE pluviometros
(
gid SERIAL PRIMARY KEY,
location GEOMETRY(POINT, 4326)
);
7.1.6.1. Comandos que não retornam resultados
Vamos criar uma função em PL/pgSQL que faça a inserção na tabela pluviometros tomando como entrada os valores de longitude e latitude onde se encontra instalado um pluviômetro:
CREATE OR REPLACE FUNCTION my_insert(longitude NUMERIC, latitude NUMERIC)
RETURNS VOID
AS
$$
BEGIN
INSERT INTO pluviometros(location)
VALUES( ST_Point(longitude, latitude, 4326) );
RETURN;
END;
$$
LANGUAGE plpgsql;
Agora, é possível chamar a função my_insert que irá montar a tupla a ser inserida na tabela pluviometros:
SELECT my_insert(-45.8872, -23.1791);
Repare que o comando INSERT sem a cláusula RETURNING não retorna nenhum resultado. Para realizar a avaliação de expressões ou consultas do tipo SELECT descartando o resultado, pode-se utilizar o comando PERFORM. Este comando é necessário, principalmente, quando se tem uma consulta do tipo SELECT cuja finalidade é apenas produzir um efeito colateral (side-effect).
PERFORM SELECT * FROM pluviometros ORDER BY gid;
Aviso
Se você quiser testar o comando acima, deverá incluí-lo dentro de uma função PL/pgSQL.
7.1.6.2. Comandos que retornam uma única tupla como resultado
Podemos também recuperar o resultado de uma consulta que retorne uma única tupla, ou seja, um resultado que pode ser composto de um ou mais valores.
Como exemplo desse tipo de comando, vamos alterar a função my_insert para que ela retorne como resultado o valor gerado automaticamente pela sequência (SEQUENCE) associada à coluna gid, que é a chave primária da tabela pluviometros.
Nota
Provavelmete você terá que remover a definição da função my_insert realizada anteriormente. Para tal, utilize o seguinte comando:
DROP FUNCTION my_insert(numeric,numeric);
CREATE OR REPLACE FUNCTION my_insert(longitude NUMERIC, latitude NUMERIC)
RETURNS pluviometros.gid%TYPE
AS
$$
DECLARE
id pluviometros.gid%TYPE;
BEGIN
INSERT INTO pluviometros(location)
VALUES( ST_Point(longitude, latitude, 4326) )
RETURNING gid INTO id;
RETURN id;
END;
$$
LANGUAGE plpgsql;
Com essa modificação, ao invocarmos a função my_insert obtemos o novo identificador da linha criada:
SELECT my_insert(-43.6419, -20.393);
Saída:
my_insert
-----------
2
(1 row)
Repare na declaração do tipo de retorno da função e no tipo da variável id. Utilizamos %TYPE para deduzir o tipo através de uma referência ao nome da coluna gid da tabela pluviometros.
Na prática, podemos associar o resultado de comandos SQL que produzam uma única tupla, a uma variável do tipo RECORD, tipos compostos (como os tipos definidos por tabelas e %ROWTYPE) ou listas de variáveis compatíveis com os tipos retornados. A sintaxe de cada comando é a seguinte:
SELECT ... INTO [STRICT] variável[, variável ...] FROM ...;
INSERT ... RETURNING expressões INTO [STRICT] variável[, variável ...];
UPDATE ... RETURNING expressões INTO [STRICT] variável[, variável ...];
DELETE ... RETURNING expressões INTO [STRICT] variável[, variável ...];
Caso a palavra STRICT seja omitida em comandos SELECT, a variável será associada ao primeiro valor retornado pelo comando ou a um valor nulo. Se a palavra STRICT for utilizada, a consulta deverá retornar uma única linha, caso contrário, será produzida uma exceção que irá interromper a execução da função. Para os comandos INSERT, UPDATE e DELETE usando a cláusula RETURNING, mesmo omitindo a palavra STRICT, será lançada uma exceção se mais de uma tupla for retornada1.
Uma variável que é automaticamente definida quando executamos um comando que retorna alguma tupla como resultado, é a variável FOUND. No caso de comandos que não usam a palavra STRICT, a próxima instrução da função pode verificar o valor desta variável para saber se alguma tupla foi ou não retornada. Por exemplo, se quisermos saber se existe algum pluviômetro a uma certa distância de uma dada localização, podemos construir uma função como a seguinte2:
CREATE OR REPLACE FUNCTION existe_pluviometro(location GEOMETRY,
distance NUMERIC)
RETURNS BOOL
AS
$$
DECLARE
pluviometro pluviometros%ROWTYPE;
BEGIN
SELECT * INTO pluviometro
FROM pluviometros
WHERE ST_DWithin(pluviometros.location,
existe_pluviometro.location,
distance);
IF FOUND THEN
RAISE NOTICE
'Encontrado pelo menos um pluviometro próximo: %',
ST_AsText(pluviometro.location);
RETURN TRUE;
ELSE
RAISE NOTICE
'Não foi encontrado um único pluviômetro nas proximidades de: %',
ST_AsText(existe_pluviometro.location);
RETURN FALSE;
END IF;
END;
$$
LANGUAGE plpgsql;
Se quisermos saber da existência ou não de um pluviômetro num raio de 1.0 grau das localizações (-45, -23) e (-47, -25), podemos realizar a seguinte consulta:
SELECT existe_pluviometro(ST_SetSRID(ST_MakePoint(-45, -23), 4326), 1.0);
Saída:
existe_pluviometro
--------------------
t
(1 row)
SELECT existe_pluviometro(ST_SetSRID(ST_MakePoint(-47, -25), 4326), 1.0);
Saída:
NOTICE: Não foi encontrado um único pluviômetro nas proximidades de: POINT(-47 -25)
existe_pluviometro
--------------------
f
(1 row)
É muito comum usarmos a variável FOUND junto com testes condicionas para lançar uma exceção:
...
IF NOT FOUND THEN
RAISE EXCEPTION 'mensagem: nenhuma linha encontrada!';
END IF;
...
No caso de comandos que usam a palavra STRICT, existem outras duas variáveis muito úteis:
NO_DATA_FOUND: indica que nenhuma tupla foi retornada.TOO_MANY_ROWS: indica que mais do que uma tupla foi retornada.
Podemos utilizar um bloco para capturar os possíveis tipos de exceções, como mostrado na função abaixo, que verifica se existe apenas um único pluviômetro a uma certa distância de uma dada localização:
CREATE OR REPLACE FUNCTION unico_pluviometro(location GEOMETRY,
distance NUMERIC)
RETURNS BOOL
AS
$$
DECLARE
pluviometro pluviometros%ROWTYPE;
BEGIN
SELECT * INTO STRICT pluviometro
FROM pluviometros
WHERE ST_DWithin(pluviometros.location,
unico_pluviometro.location,
distance);
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE NOTICE 'Nenhum pluviômetro encontrado próximo a: %',
ST_AsText(unico_pluviometro.location);
RETURN FALSE;
WHEN TOO_MANY_ROWS THEN
RAISE NOTICE 'Vários pluviômetros encontrados próximo a: %',
ST_AsText(unico_pluviometro.location);
RETURN FALSE;
END;
$$
LANGUAGE plpgsql;
Para testar a função acima, execute os comandos abaixo:
SELECT unico_pluviometro(ST_SetSRID(ST_MakePoint(-45, -23), 4326), 1.0);
Saída:
unico_pluviometro
-------------------
t
(1 row)
SELECT unico_pluviometro(ST_SetSRID(ST_MakePoint(-47, -25.), 4326), 1.0);
Saída:
unico_pluviometro
-------------------
f
(1 row)
Aviso
Atente-se ao fato de que a variável FOUND é sempre associada ao valor TRUE quando STRICT é utilizada e o comando retorna uma única tupla.
7.1.6.3. Comandos Dinâmicos
Outra facilidade para criação de funções em PL/pgSQL é a possibilidade de construirmos dinamicamente uma string representando um comando SQL para posterior execução. Vamos ilustrar o uso do comando EXECUTE com um exemplo que irá posicionar um pluviômetro em uma localização aleatória.
CREATE OR REPLACE FUNCTION random_insert()
RETURNS pluviometros.gid%TYPE
AS
$$
DECLARE
id pluviometros.gid%TYPE;
r NUMERIC;
longitude NUMERIC;
latitude NUMERIC;
pt GEOMETRY;
query TEXT;
BEGIN
query := 'INSERT INTO pluviometros (location) VALUES($1) RETURNING gid';
r := random();
longitude := 360.0 * r - 180.0;
latitude := 180.0 * r - 90.0;
RAISE NOTICE 'Localização: (%, %)', longitude, latitude;
pt := ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
EXECUTE query INTO STRICT id USING pt;
RETURN id;
END;
$$
LANGUAGE plpgsql;
Executando a função random_insert obtemos um resultado como:
SELECT random_insert();
Saída:
NOTICE: Localização: (17.3685365170239600, 8.6842682585119800)
random_insert
---------------
3
(1 row)
Aviso
Os parâmetros como $1 e $2 só podem ser usados para os valores de atributos. Não é possível utilizá-los para substituir nomes de tabelas ou colunas. Neste caso, devemos concatenar o texto do comando ou usar a função format() com o caractere de substituição de nomes de tabelas e colunas (%I).
Notas
- 1
A explicação dada pelo time de desenvolvimento do PostgreSQL é que neste caso não há a possibilidade de usar uma cláusula como
ORDER BYque possibilite a escolha da linha afetada a ser considerada.- 2
Obviamente não precisamos de uma função para isso, além de podermos escrever uma consulta de agregação com o operador
COUNT(*).