No PostgreSQL, funções personalizadas podem ser criadas para resolver problemas complexos.

Eles podem ser escritos usando a linguagem de script PL/pgSQL padrão ou podem ser escritos em outra linguagem de script.

Python, Perl, Tcl e R são algumas das linguagens de script suportadas.

Embora o PL/pgSQL venha com qualquer instalação do Postgres, usar outras linguagens requer alguma configuração.

Instalando a extensão

Antes que uma extensão possa ser usada, o pacote de extensão precisa ser instalado.

No Ubuntu você executaria:

Perl

sudo apt-get -y install postgresql-plperl-14

O nome do pacote 'postgresql-plperl-14' é específico do PostgreSQL versão 14. Se estiver usando uma versão diferente do PostgreSQL, você precisará alterar o número da versão no nome do pacote para corresponder à versão instalada do PostgreSQL.

Python 3

sudo apt-get install postgresql-plpython3-14

Ativando a extensão

Para ativar a extensão no PostgreSQL, a extensão deve ser definida usando a instrução CREATE EXTENSION.

Perl

CREATE EXTENSION plperl;

Pitão

CREATE EXTENSION plpython3;

Olá mundo exemplo

Depois que a extensão for criada, uma função personalizada poderá ser criada usando a extensão.

Perl

CREATE OR REPLACE FUNCTION hello(name text) 
RETURNS text AS $$
    my ($name) = @_;
    return "Hello, $name!";
$$ LANGUAGE plperl;

Pitão

CREATE OR REPLACE FUNCTION hello(name text)
RETURNS text AS $$
    return "Hello, "   name   "!"
$$ LANGUAGE plpython3;

Dividindo linha por linha

CREATE OR REPLACE FUNCTION hello(name text)

Esta linha é como uma função é criada no Postgres. Ao usar CREATE OR REPLACE, ele substituirá qualquer função já definida com o nome hello pela nova função.

Usar CREATE FUNCTION hello(name text) impedirá que a função substitua uma função existente e causará erro se a função já existir.

RETURNS text AS $$

Isso define qual tipo de dados do Postgres será retornado, é importante que o tipo de dados especificado seja um tipo reconhecido pelo Postgres. Um tipo de dados personalizado pode ser especificado, se o tipo personalizado já estiver definido.

$$ é um delimitador para marcar o início e o fim de um bloco de código. Nesta linha está marcando o início do bloco de código.

Todo o código entre o início e o fim $$ será executado pelo Postgres

$$ LANGUAGE plperl;

$$ denota o final do script e informa ao Postgres em qual idioma o script deve ser analisado.

Usando a função

As funções podem ser usadas como qualquer função integrada do Postgres

SELECT hello('world');

Isso retornará uma coluna com o valor Olá, mundo!

As funções podem fazer parte de consultas mais complexas:

SELECT id, title, hello('world') greeting FROM table;

Exemplo mais complexo

Aqui está um exemplo de função que aceita texto de um campo e retorna uma contagem de palavras.

CREATE OR REPLACE FUNCTION word_count(paragraph text)
RETURNS json AS $$
use strict;
use warnings;

my ($text) = @_;

my @words = $text =~ /\w /g;
my $word_count = scalar @words;

my $result = '{' .
    '"word_count":' . $word_count .
'}';
return $result;
$$ LANGUAGE plperl;

Isso retorna um resultado formatado em JSON com a contagem de palavras.

Podemos adicionar estatísticas mais detalhadas à função.

CREATE OR REPLACE FUNCTION word_count(paragraph text)
RETURNS json AS $$
use strict;
use warnings;

my ($text) = @_;

my @words = $text =~ /\w /g;

my $word_count = scalar @words;

my $sentence_count = ( $text =~ tr/!?./!?./ ) || 0;

my $average_words_per_sentence =
  $sentence_count > 0 ? $word_count / $sentence_count : 0;

my $result = '{' .
    '"word_count":' . $word_count . ',' .
    '"sentence_count":' . $sentence_count . ',' .
    '"average_words_per_sentence":"' . sprintf("%.2f", $average_words_per_sentence) . '"' .
'}';

return $result;
$$ LANGUAGE plperl SECURITY DEFINER;

Agora, quando usamos em uma consulta

SELECT word_count(text_field) word_count FROM table

Ele retornará JSON como

{"word_count":116,"sentence_count":15,"average_words_per_sentence":"7.73"}

Considerações de segurança

Ao usar funções personalizadas ou linguagens de script externas, há considerações de segurança adicionais a serem levadas em consideração. Pode ser um malabarismo para obter o equilíbrio certo entre usabilidade e segurança.

Definidor de segurança vs invocador de segurança

Na função anterior, a opção SECURITY DEFINER foi adicionada à instrução de criação da função.

É importante pensar em como você deseja que uma função seja executada do ponto de vista da segurança.

O comportamento padrão é usar SECURITY INVOKER. Isso executará a função com os privilégios do usuário que está executando a função.

SECURITY DEFINER fornece mais controle sobre os privilégios concedidos à função. Usando este modo, a função será executada com os privilégios do usuário que a criou.

Isso pode ser bom ou ruim, se uma função for criada por um usuário com privilégios limitados, haverá pouco dano que poderá ser causado ao banco de dados.

Se a função for criada por um usuário com altos privilégios de acesso, a função será executada com esses mesmos privilégios. Dependendo do tipo de função, isso pode permitir que um usuário execute a função com mais privilégios abertos do que os concedidos.

Há momentos em que isso é útil, por exemplo, se um usuário não tiver privilégios de leitura para uma tabela, mas dentro da função, a leitura é necessária, o uso do SECURITY DEFINER pode permitir os privilégios de leitura necessários para a execução da função.

Extensões confiáveis ​​e não confiáveis

Ao criar as extensões acima, plperl e plpython3 foram usados. Na maioria das circunstâncias, essas são as extensões corretas a serem usadas.

Essas extensões têm acesso limitado ao sistema de arquivos dos servidores e às chamadas do sistema.

Extensões também podem ser criadas com um u (plpython3u, plperlu)

Estas são extensões não confiáveis ​​e permitem mais acesso ao sistema de arquivos dos servidores.

Pode haver casos em que isso seja necessário, por exemplo, se você quiser usar módulos Perl, bibliotecas Python ou usar chamadas de sistema.

No exemplo acima, a saída JSON foi gerada como uma string, se desejado, o módulo Perl JSON poderia ter sido usado para codificar os dados como JSON. Para fazer isso, seria necessário usar a extensão não confiável para acessar o módulo JSON.

É aconselhável não usar extensões não confiáveis, mas se necessário, use com cautela e entenda os riscos potenciais.

Se Perl estiver sendo usado, Perl será executado no modo taint quando a extensão não confiável estiver em uso.

Pensamentos finais

Ser capaz de aproveitar as vantagens do processamento de texto avançado e gerenciamento de memória do Perl ou das bibliotecas de análise de dados do Python no PostgreSQL pode ser uma ferramenta realmente poderosa.

Transferir tarefas complexas para ferramentas mais adequadas para lidar com a tarefa pode reduzir a sobrecarga no banco de dados.

Como sempre, ao usar funções personalizadas e linguagens de script externas, tome precauções para garantir o uso seguro.