PostgreSQL Stored Procedures: Funções em Linguagem SQL

Em muitos SGDBs temos o conceito de Stored Procedures, programas desenvolvidos em uma determinada linguagem de script e armazenados no servidor, onde serão processados. No PostgreSQL, as Stored Procedures são conhecidas com o nome de Functions.

Tipos de funções

No PostgreSQL podemos ter três tipos de funções:

• Funções em Linguagem SQL: As funções em SQL não possuem variáveis e estruturas de comando (if, for etc). Elas apenas consistem em uma lista de comandos SQL (SELECT, INSERT, DELETE ou UPDATE), devendo retornar, obrigatoriamente, um determinado valor. Assim, o último comando deve ser sempre um SELECT. Essas funções são carregadas juntamente com o serviço do PostgreSQL, não necessitando de nenhuma carga de módulo adicional.
• Funções de Linguagens Procedurais: Esse tipo de função utiliza variáveis e estruturas de comandos, além de executar ações SQL. Na versão atual do PostgreSQL temos quatro tipos de linguagens procedurais: PL/PgSQL, PL/Tcl, PL/Perl e PL/Python. A Linguagem PL/PgSQL é a mais utilizada, pois é bem estruturada e fácil de aprender. As linguagens PL/Tcl, PL/Perl e PL/Python têm sintaxe semelhante às linguagens das quais elas herdam sua implementação. Sua utilização será explorada nas próximas edições da SQL Magazine.
  Nota: A linguagem PL/PgSQL é semelhante a linguagem PL/SQL, do Oracle.
• Funções Externas: No PostgreSQL podemos utilizar funções desenvolvidas em uma linguagem externa, como C++. A vantagem é que passamos a contar com o poder de uma linguagem de programação completa, possibilitando a implementação de rotinas complexas no banco de dados. As funções devem ser empacotadas em bibliotecas compatilhadas que, por sua vez, devem ser registradas no SGBD.

Este artigo apresenta as funções em SQL. Os demais tipos de função serão temas para as próximas edições da revista.

Desenvolvendo Funções em SQL

Tomaremos como exemplo o modelo de dados da figura a seguir. Os exemplos aqui descritos foram executados no Linux.

Para criação do modelo, execute os procedimentos:

1. Crie um banco de dados chamado sqlmagazine com o comando createdb:
   

   createdb -U postgres sqlmagazine 

2. Abra o banco de dados, com o comando:
   

   psql -U postgres sqlmagazine

3. No psql, crie os objetos do modelo e insira alguns registros nas tabelas. A listagem completa para este procedimento está disponível para download no site deste artigo.

O Comando Create Function

Para funções implementadas em SQL, o comando create function tem a seguinte estrutura:

CREATE [OR REPLACE] FUNCTION NomeDaFuncao([parâmetro 1, parâmetro 2, parâmetro n])
     RETURNS RetornoTipoDeDados AS '
              Corpo da Função;
     '
     LANGUAGE 'SQL';

• CREATE FUNCTION - Define o nome da função e seus respectivos parâmetros, caso existam. Esses parâmetros são identificados internamente como $1 (Parâmetro 1), $2 (Parâmetro 2), $3 (Parâmetro 3), e assim por diante.
• RETURNS RetornoTipoDeDados - Indica o tipo de dado de retorno da função. Uma função pode retornar um tipo simples como integer, varchar etc. Funções em SQL também podem retornar um conjunto de valores ou uma estrutura composta de várias linhas (resultset).
• Corpo da Função - Contém a implementação da função e deve estar entre aspas simples.
• LANGUAGE ‘SQL’ - indica que a linguagem utilizada para implementação da função é SQL (se estivéssemos utilizando a linguagem PL/PgSQL, usaríamos LANGUAGE ‘PLPGSQL’);

Vejamos um exemplo de criação de uma função SQL. Esta deve ser digitada em um editor de textos e gravada, por exemplo, em /root, com o nome de funcao01.sql. Esta função recebe como parâmetro um único valor do tipo inteiro (identificado como $1), e também retorna um valor do tipo inteiro. O valor de retorno é definido pela execução do último comando da rotina (neste exemplo, o único comando existente):

 
     CREATE FUNCTION incrementar(INTEGER)
     RETURNS INTEGER AS '
              SELECT $1 + 1 ;
     '
     LANGUAGE 'SQL';

Para executar a função, devemos utilizar o comando SELECT. Veja o exemplo:

sqlmagazine=# SELECT incrementar(10);
      
      incrementar 
     -------------
               11
     (1 row)
     sqlmagazine=#

O próximo exemplo retorna o número de contas que um determinado cliente possui. O id do cliente deverá ser passado como parâmetro:

 CREATE FUNCTION ncontas(INTEGER)
     RETURNS INT8 AS '
       SELECT COUNT(*) FROM contas 
          WHERE fkcliente = $1;
     '
     LANGUAGE 'SQL';

Após carregar a função no banco de dados, utilize o comando SELECT para executá-la:

SELECT ncontas(2);

O exemplo a seguir insere dados referentes ao cliente e sua conta, retornando o id do cliente inserido:

CREATE FUNCTION cliente_contadesc(VARCHAR(30), VARCHAR(30))
     RETURNS INT8 AS '
              INSERT INTO clientes(nome) VALUES($1);
              INSERT INTO contas(fkcliente, descricao) 
                  VALUES(CURRVAL(''clientes_id_seq''),$2);
              SELECT CURRVAL(''clientes_id_seq'');  
     '
     LANGUAGE 'SQL';

Observe que o código principal é implementado entre aspas simples e o parâmetro da função currval(), que é um string, tem de ser passado entre duas aspas simples. O último comando retorna o id do cliente que acabou de ser inserido.

Após carregar a função no banco de dados, utilize o comando a seguir para executar a função:

SELECT cliente_contadesc('SILVIO','SEMANA04');

NOTA: Para apagar uma função, utilize o comando: drop function <nome_da_funcao()>;</nome_da_funcao()>

Retornando um conjunto de registros e campos

Em funções SQL podemos retornar um conjunto de linhas; para isso, devemos acrescentar o parâmetro SETOF antes do tipo de dado a ser retornado. Como exemplo, criaremos agora uma função para retornar o id dos clientes com saldo negativo.

CREATE FUNCTION quemdeve() RETURNS SETOF INTEGER AS '
              SELECT clientes.id FROM clientes 
              INNER JOIN contas ON clientes.id = contas.fkcliente
              INNER JOIN movimentos ON contas.id = movimentos.fkconta
              GROUP BY clientes.id
              HAVING SUM(movimentos.credito - movimentos.debito) < 0;
     '
     LANGUAGE 'SQL';

Utilize o comando SELECT quemdeve() para executar a função. O resultado exibido é o seguinte:

 quemdeve  ----------
             1
             2
     (2 rows)

Caso seja necessário retornar todas as colunas da tabela CLIENTES, o tipo de dados de retorno da função deve referenciar a tabela ou uma view que possua uma estrutura compatível. Funções que retornam tipos equivalentes a tabelas estão disponíveis somente a partir da Versão 7.3 do PostgreSQL. Veja um exemplo:

CREATE FUNCTION devedores()
     RETURNS SETOF clientes AS '
              SELECT * FROM clientes WHERE id IN 
              (
                        SELECT clientes.id FROM clientes 
                        INNER JOIN contas ON clientes.id = contas.fkcliente
                        INNER JOIN movimentos ON contas.id = movimentos.fkconta
                        GROUP BY clientes.id
                        HAVING SUM(movimentos.credito - movimentos.debito) < 0
              );
     '
     LANGUAGE 'SQL';

Uma função que retorna um resultset deve ser chamada de forma semelhante a qualquer tabela do banco de dados, ou seja, na cláusula FROM. Veja um exemplo:

select id, nome from devedores();  id | nome  
     ----+-------
       1 | MARIA
       2 | JOSE
     (2 rows)

CREATE FUNCTION MaioresClientes(NUMERIC(15,2))
     RETURNS SETOF clientes AS '
              SELECT * FROM clientes WHERE id IN 
              (
                        SELECT clientes.id FROM clientes 
                        INNER JOIN contas ON clientes.id = contas.fkcliente
                        INNER JOIN movimentos ON contas.id = movimentos.fkconta
                        GROUP BY clientes.id
                        HAVING SUM(movimentos.credito) >= $1
              );
     '
     LANGUAGE 'SQL';

Nesse caso, se quisermos a listagem dos clientes que movimentaram mais de R$ 10.000,00, podemos executar o comando:

select id, nome from MaioresClientes (10000);

Onde se encontram as functions?

A tabela de sistema pg_proc armazena todas as functions que foram criadas. Para visualizá-las, utilize a seguinte query:

SELECT proname, prosrc FROM pg_proc WHERE proname = 'quemdeve';

NOTA: Todas as tabelas de sistema do PostgreSQL são iniciadas por ‘pg’

Conclusão

Funções em Linguagem SQL constituem-se de Queries que são armazenadas no servidor, podendo ou não receber parâmetros. Elas podem retornar valores ou conjuntos de registros.

O uso de funções SQL torna-se interessante devido à simplicidade de sua implementação. Caso o processamento a ser implementado seja mais complexo, a ponto de requerer a utilização de estruturas de controle, condicionais, variáveis etc, a utilização de PL/PGSQL ou até do C é mais recomendada. Na próxima edição abordaremos mais detalhes sobre esse assunto.

PARTE II
Veja abaixo a segunda parte do artigo - Agora as partes I e II foram compiladas em um único artigo. Bons estudos :)

PostgreSQL Stored Procedures: Funções e Triggers - Parte 2

Clique aqui para ler todos os artigos desta edição

A primeira parte deste artigo definiu o conceito de funções (utilização e tipos) e abordou a criação e o uso de funções do tipo SQL. Este artigo trata de outro tipo de função que pode ser implementada no PostgreSQL, mais conhecida como função de linguagens procedurais. Essas funções, além de executarem comandos SQL, utilizam recursos de variáveis e estruturas de controle fundamentais à qualquer linguagem procedural.

Na versão atual do PostgreSQL, temos 4 tipos de linguagens procedurais: PL/PgSQL, PL/Tcl, PL/Perl e PL/Python. Ao contrário das funções em SQL, é necessário carregar a linguagem de desenvolvimento no banco de dados como um módulo. Esse procedimento também será descrito no decorrer deste artigo.

A sintaxe da PL/PgSQL é muito semelhante à da PL/SQL do Oracle. As linguagens PL/Tcl, PL/Perl e PL/Python possuem características próprias das linguagens que herdam, e são pouco utilizadas. Em função disso, abordaremos a sintaxe de funções no PostgreSQL por meio da linguagem procedural PL/PgSQL. Assim como no artigo anterior, todos os procedimentos foram executados em ambiente Linux.

Processo de criação de Stored Procedures em PL/PgSQL

No PostgreSQL, as linguagens procedurais não funcionam como as funções em SQL, onde podemos criar diretamente uma função e, em seguida, executá-la no banco de dados. Para criarmos uma stored procedure em uma linguagem procedural PL/PgSQL, devemos primeiro carregar no banco de dados o módulo da linguagem a ser utilizada para desenvolver a função. Essa regra se aplica também a outras linguagens procedurais, como PL/Tcl, PL/Perl e PL/Python.

Vamos desenvolver nossas funções em PL/PgSQL no banco sqlmagazine criado no artigo anterior. Utilizaremos o comando createlang para carregar a linguagem PL/PgSQL nesse banco de dados. No prompt do Linux, execute o comando abaixo:

createlang –U postgres plpgsql sqlmagazine

Com este comando, carregaremos o módulo da linguagem procedural PL/PgSQL somente no banco sqlmagazine. O PostgreSQL possui um banco padrão denominado template1. Todos os databases que criamos copiam a estrutura desse banco padrão, portanto, se quisermos que a linguagem PL/PgSQL esteja disponível em todos os bancos no servidor, devemos executar o seguinte comando:

createlang –U postgres plpgsql template1

Estrutura de uma Função em PL/PGSQL

A Listagem 1 mostra a sintaxe geral para a criação de uma função em PL/PgSQL:

Listagem 1 - Sintaxe para a criação de funções

CREATE [OR REPLACE] FUNCTION nome_da_procedure(parametro1,
     parametro2, ..., parametroN)
     RETURNS [SETOF] tipo_dado_retornado AS ‘
     DECLARE
     variaveis;
     BEGIN
     algoritmo_da_procedure;
     END;
     ‘ LANGUAGE ‘PLPGSQL’;

Observando a Listagem 1 temos:

O comando CREATE FUNCTION: cria uma função. Para alterar ou sobrescrever uma função já existente, devemos utilizar CREATE OR REPLACE FUNCTION.

Em nome_da_procedure, temos o nome da procedure a ser criada. Aqui poderíamos mencionar o nome do Schema do qual a função faria parte. Ao omitirmos o nome do Schema, a procedure será criada sobre um Schema padrão denominado public.

Em (parametro1, parametro2,..., parametroN), caso tenham sido declarados, definimos o tipo de dado dos parâmetros de entrada da função. Esses parâmetros serão acessados na implementação da procedure como $1 (Parâmetro 1), $2 (Parâmetro 2) , ..., $N (Parâmetro N). Os tipos de dados dos parâmetros podem ser: base (como int, int2, int4, int8, char, varchar) ou colunas (referenciados como nome_da_tabela.coluna%type). Desse modo, o tipo de dado do parâmetro de entrada será definido e estará vinculado ao tipo de dado do campo indicado. Tanto a definição dos parâmetros como o acesso a eles funcionam da mesma forma que em funções SQL.

Em tipo_de_dados_retornado mencionamos o tipo de dado de retorno da procedure, que poderá ser base ou coluna. Toda procedure deverá retornar uma informação. O modificador SETOF indica que a procedure retornará um conjunto de linhas em vez de uma só linha.

Na linha 2 usamos o comando declare para a declaração de variáveis. As variáveis poderão ser do tipo alias, base, colunas ou record. Vale a pena destacarmos aqui alguns conceitos referentes à declaração de variáveis. Observe os exemplos a seguir:

declare
     idvenda ALIAS FOR $1;
     idcomprador INT4;
     idusuario usuarios.id%TYPE;
     r RECORD;
     rec usuarios%ROWTYPE;

No exemplo acima, usamos o comando declare com os seguintes valores:

idvenda ALIAS FOR $1;

Com esta declaração, definimos a variável idvenda como um apelido do parâmetro $1 passado para a procedure. Usamos esse artifício para poder substituir a string $1 no algoritmo principal de nossa procedure por um nome de fácil compreensão.

idcomprador INT4;

Estamos definindo uma variável denominada idcomprador com o tipo de dados inteiro de 4 bytes.

idusuario usuarios.id%TYPE;

Definimos a variável idusuario de acordo com o tipo de dados da coluna id da tabela usuarios. Dessa forma, teremos um vínculo entre ambos. Com isso, quando o tipo de dados da coluna id na tabela usuarios for alterado, o tipo de dados da variável idusuario será automaticamente alterado também.

r RECORD;

Aqui definimos que r será uma variável do tipo record . Esse tipo de variável permite trabalhar com os registros e campos de determinada tabela em estruturas de controle.

rec usuarios%ROWTYPE;

As variáveis %ROWTYPE definem o tipo de dados de uma variável de acordo com o tipo de dados de uma linha inteira de determinada tabela. Definimos a variável rec como um tipo de dados de acordo com toda a linha da tabela usuarios. Dessa forma, teremos um vínculo entre ambos e, quando um tipo de dados de qualquer uma das colunas da tabela usuarios for alterado, os valores de rec serão alterados de acordo.

Após a DECLARE, temos a seção de implementação da procedure, delimitada por BEGIN e END. É importante ressaltar que BEGIN e END não têm nenhum vínculo com os conceitos de transação do PostgreSQL.

Por fim, temos a declaração da linguagem utilizada para criar a procedure. Nesse caso, utilizamos a LANGUAGE ‘PLPGSQL’.

Elementos da Linguagem PL/

PgSQL

1. Estruturas Condicionais

Assim como em outras linguagens, os testes lógicos são efetuados com a estrutura condicional IF-THEN-ELSE. A expressão booleana pode comparar valores de diversos tipos de dados e pode ter expressões select em sua composição. No geral temos:

IF expressão-boleana THEN
     comandos
     ELSE
     comandos
     END IF;
    

Como exemplo, tendo V_COUNT como um número inteiro, temos:

IF v_count > 0 THEN
     INSERT INTO users_count(count)
     VALUES(v_count);
     return ‘’v’’;
     ELSE
     return ‘’f’’;
     END IF;
    

2. Estruturas de repetição e laços condicionais

Para estruturas que implementam laços ou loops temos o LOOP-END LOOP, FOREND LOOP, WHILE-END LOOP e o que chamamos de QUERY-LOOP. Seguem as sintaxes:

LOOP-END LOOP
      
     LOOP
     comandos
     END LOOP
    ;

Para ‘encerrar’ ou sair de um loop, utilizamos o comando exit. Esse comando pode ser utilizando para interromper o loop em todas as estruturas aqui descritas.

FOR-END LOOP

FOR variavel IN [ valores ] expressão
     .. LOOP
     Comandos
     END LOOP;
    

O exemplo abaixo faz um loop com a variável i, alterna seu valor entre 1 e 10 e retorna à aplicação cliente o valor da variável no loop por meio da função RAISE NOTICE. Esta função retorna uma informação qualquer ao client (como, por exemplo, um printf do C). Sua sintaxe é RAISENOTICE mensagem, variavel. O conteúdo de variável pode ser utilizado na mensagem e ser substituído automaticamente pelo Postgre quando ele encontrar o símbolo % na mensagem. Segue o exemplo:

FOR i IN 1..10 LOOP
     Comandos
     
    RAISE NOTICE ‘’i vale %’’,i;
     END LOOP;
    

WHILE expressão LOOP
    Comandos
    END LOOP;

QUERY-LOOP

Você pode implementar um laço condicional diretamente com o resultado de um select, algo como o “While Not DataSet.Eof Do” do Delphi. Para isso, é possível utilizar variáveis do tipo Record ou %ROWTYPE. A sintaxe geral dessa estrutura é a seguinte:

FOR variavel_record_ou_%rowtype IN select
     LOOP
     Comandos
     END LOOP;
     
    

Segue um exemplo da utilização dessa estrutura de repetição, na qual os dados de uma tabela são inseridos em outra tabela que possui a mesma estrutura:

DECLARE
     registro RECORD;
     BEGIN
     Comandos;
     FOR registro IN SELECT * FROM TABELA1
     ORDER BY ID LOOP
     INSERT INTO TABELA2(ID, DESCRICAO)
     VALUES (registro.ID, registro.DESCRICAO);
     END LOOP;
     Comandos;
     END;

3. Executando QUERYS em expressão texto.

O PL/PgSQL possui um comando chamado EXECUTE. Este comando executa uma instrução SQL em formato texto. A sintaxe geral é

EXECUTE ‘expressão SELECT’;

A Listagem 2 mostra a implementação de uma função que executa um select dinâmico na tabela passada como parâmetro. Neste exemplo, declaramos duas variáveis: vtabela (que é um ALIAS para o parâmetro $1) e vSQL (que receberá a composição do comando ‘SELECT * FROM’ com o nome da tabela passada como parâmetro).

Listagem 2 - Sintaxe para a criação de uma função que executa um Select dinâmico

CREATE FUNCTION select_dinamico (text) RETURNS INTEGER AS’
     DECLARE
     vtabela alias for $1;
     vSQL text;
     BEGIN
     vSQL := ‘SELECT * FROM ‘ || vtabela;
     EXECUTE vSQL;
     RETURN 1;
     END;
     ‘ LANGUAGE ‘plpgsql’;
    

O EXECUTE também pode ser utilizado nos QUERY-LOOPS exemplificados anteriormente. A sintaxe geral para sua utilização é a seguinte:

FOR variavel_record_ou_%rowtype IN EXECUTE
     STRING LOOP
     Comandos
     END LOOP;
    

Desenvolvendo em PL/PgSQL

Começaremos pelo desenvolvimento de uma Function simples, na qual passaremos como parâmetro o id de um cliente. Se o cliente existir, será retornado o seu nome; caso contrário, será retornado uma mensagem do tipo “Cliente id X não existe”. Utilizando um editor de textos simples, edite a Function da Listagem 3 e salve-a em /root com o nome de proc01.sql.

Listagem 3 - Implementação de Function em PL/PgSQL

-- function proc01.sql
     --
     CREATE OR REPLACE FUNCTION id_nome_cliente(INTEGER) RETURNS TEXT AS ‘
     DECLARE
     r RECORD;
     BEGIN
     SELECT INTO r * FROM clientes WHERE id = $1;
     IF NOT FOUNT THEN
     RAISE EXCEPTION ‘’Cliente % não existente !’’, $1;
     END IF;
     RETURN r.nome;
     END;
     ‘
     LANGUAGE ‘PLPGSQL’;
    

Nota: No PL/PgSQL, os comentários das linhas em SQL iniciam com –, e os comentários dos blocos de linhas estarão entre /* string comentário */.

A Function criada na Listagem 3 utiliza um record para armazenar o conteúdo do registro de retorno, e o modificador NOT FOUNT retorna um boolean que indica se o último SELECT executado retornou registros. O RAISE EXCEPTION funciona de forma semelhante ao RAISE NOTICE explicado anteriormente, porém gera uma exceção e aborta a execução da Function. Para carregar essa Function no banco de dados, execute os seguintes procedimentos:

1. No prompt do Linux, abra o banco de dados com o seguinte comando:
   

   psql -U postgres sqlmagazine

2. O banco sqlmagazine será aberto. Carregue a Function com o comando \inome_procedure.sql. A Listagem 4 mostra o prompt de inicialização do banco e a execução do comando de carga da função criada.

Listagem 4 - Prompt de inicialização e carga da função implementada

Welcome to psql 7.3.3, the PostgreSQL interactive terminal.
      
     Type: \copyright for distribution terms
     \h for help with SQL commands
     \? for help on internal slash commands
     \g or terminate with semicolon to execute query
     \q to quit
      
     sqlmagazine=#\i proc01.sql
     CREATE FUNCTION
     sqlmagazine=#

3. Para executar a Function, use o comando select nome_da_procedure(). A Listagem 5 mostra a execução da procedure e o resultado de seu processamento para um cliente cadastrado e para um cliente nãocadastrado.

Listagem 5 - Execução da procedure criada na Listagem 4

sqlmagazine=# SELECT id_nome_cliente(2);
     id_nome_cliente
     -----------------
     JOSE
     (1 row)
      
     sqlmagazine=# SELECT id_nome_cliente(2000);
     ERROR: Cliente 2000 não existente !
     sqlmagazine=#

Os procedimentos aqui descritos podem ser utilizados para criação, carga e execução de qualquer Function no PostgreSQL. Para consultar as procedures criadas no seu banco de dados, execute um SELECT sobre a tabela de sistema que armazena todas as procedures. Esse select retornará inclusive o corpo da implementação da procedure criada. Consulte a Listagem 6.

Listagem 6 - Verificando as procedures criadas no banco de dados

sqlmagazine=# SELECT proname, prosrc FROM pg_proc WHERE proname = ‘id_nome_cliente’;
      
     proname      |      prosrc
      
     -----------------+-------------------------------------------------------------------
     id_nome_cliente | DECLARE
     r RECORD;
     BEGIN
     SELECT INTO r * FROM clientes WHERE id = $1;
     IF NOT FOUND THEN
     RAISE EXCEPTION ‘Cliente % não existente !’, $1;
     END IF;
     RETURN r.nome;
     END;
      
     (1 row)
      
     sqlmagazine=#

Triggers

A implementação da Trigger é feita por meio da criação de um objeto que envia uma chamada para uma função, que por sua vez executará a ação. Por exemplo, a Listagem 7 implementa a função ftr_ins_ teste() que será chamada por uma trigger. Isso permite que as triggers no PostgreSQL sejam implementadas em diversas linguagens de desenvolvimento, como JAVA, PERL, C, TCL etc.

Listagem 7 - Criação de uma Function para implementação de uma Trigger

Create table teste(id int4, nome text);
     Create table teste2(id_teste int4, nome text);
      
     1    create or replace Function ftr_ins_teste() returns trigger as’
     2    Begin
     3       if new.id is not null then
     4          insert into teste2(id_teste, nome) values(new.id, new.nome);
     5       end if;
     6       return new;
     7    end;
     8    ‘Language ‘plpgsql’;

Para executar o exemplo da Listagem 7, crie as seguintes tabelas:

Vamos analisar a implementação da Function:

Linha 1: create or replace Function ftr_ins_geuf() returns trigger as’

Observamos que o tipo de retorno aqui é diferente: a Function a ser utilizada numa Trigger tem um tipo de retorno específico, definido como trigger.

Linha 2: Begin Inicialização do código de implementação da Function, mas poderíamos ter um declare exatamente igual a uma função, pois a linguagem é PL/PgSQL.

Linha 3: if new.id is not null then O modificador new possibilita o acesso ao novo valor atribuído às colunas da tabela que disparou a trigger.

Linha 4: insert into teste2(id_teste, nome) values(new.id, new.nome);

Insere o registro da tabela que disparou a trigger na Tabela 2, caso a coluna id não seja nula (condição testada na linha 3).

Linha 6: return new; No caso de Functions que serão utilizadas em triggers de insert, o result recebe sempre o modificador NEW. No caso de triggers de Update, o result pode ser NEW ou OLD (para deletes, apenas OLD).

Nota: os modificadores NEW e OLD são variáveis do tipo RECORD.

Uma vez criada a função PL/PgSQL com os procedimentos citados anteriormente, passaremos então à criação da trigger que chamará a função. Use o comando a seguir:

Create trigger tr_ins_teste after insert
     on teste for each row execute procedure
     ftr_ins_teste();

Neste comando, temos:

Create trigger tr_ins_teste - o comando create da trigger. A sintaxe genérica é CREATE TIGGER NOME_TRIGGER;

after insert - esta é uma parte interessante da criação. Estamos definindo aqui que a ação será realizada depois de ocorrer o insert na tabela teste. Os eventos (momentos de execução da função) possíveis para a uma trigger são:

• AFTER INSERT ON TABELA
• BEFORE INSERT ON TABELA
• AFTER UPDATE ON TABELA
• BEFORE UPDATE ON TABELA
• AFTER DELETE ON TABELA
• BEFORE DELETE ON TABELA

on teste - dizemos aqui que a trigger está vinculada à tabela teste.

for each row execute procedure ftr_ins_teste() - aqui é feita a chamada da função para a trigger que criamos, ftr_ins_teste().

Conclusão

Neste artigo, mostro os elementos de sintaxe da linguagem PL/PgSQL e os procedimentos de criação, alteração e carga no banco de dados de uma Function. O uso dessa linguagem alarga o horizonte do desenvolvedor e disponibiliza recursos e estruturas não encontradas nas funções implementadas em C. Fizemos também uma rápida análise sobre a implementação de trigger (assunto extenso que será abordado em mais detalhes em futuros artigos). No próximo artigo dessa série, vamos implementar as funções por meio da linguagem C e explorar seus recursos e utilizações. Até lá.

PARTE III
Veja abaixo a terceira e última parte do artigo - Agora as partes I, II e III foram compiladas em um único artigo. Bons estudos :)

Desenvolvimento de funções (Stored Procedures) em PostgreSQL: Funções em C - Parte 3

Atenção: por essa edição ser muito antiga não há arquivo PDF para download.Os artigos dessa edição estão disponíveis somente através do formato HTML.

Clique aqui para ler todos os artigos desta edição

Os dois primeiros artigos dessa série abordaram os fundamentos para desenvolvimento e utilização de funções no PostgreSQL utilizando SQL e PL/PGSQL. Essas duas linguagens oferecem ao desenvolvedor PostgreSQL recursos para construir sistemas complexos com finalidades diversas. Mas para customizações que exigem um nível maior de complexidade e recursos - não disponíveis na linguagem SQL ou PL/PGSQL – o PostgreSQL permite a utilização do C.

Mas porque utilizar C? Como vimos nos artigos anteriores, o PostgreSQL trabalha com várias linguagens: JAVA, TCL, PERL, PHYTON, etc. Um bom motivo de adotarmos o C é a velocidade de processamento, superior às outras abordagens e a limitação do PL/PGSQL em nível de programação. Enquanto o PL/PGSQL é uma linguagem interpretada, o C é compilado.

Primeiros passos em desenvolvimento de funções em C

A implementação de funções em C a serem utilizadas pelo PostgreSQL consiste basicamente de três passos:

1. Implementação da função

A listagem 1 traz um exemplo clássico de uma função que recebe como argumento um número inteiro e retorna o dobro do valor do número passado.

#include
      extern int double_me(int a)
     {
         return a*2;
     }

Listagem 1 – Código fonte C para implementação da função.

2. Compilação da mesma para criação do .SO no Linux

Como nosso ambiente de desenvolvimento é linux, vamos compilar a função para criação do objeto SO. Para tal, salve o arquivo com o nome de double.c e compile o mesmo com o seguinte comando a partir do shell do linux:

gcc -shared -Wl,-soname, libpgdouble.so.1 -o libpgdouble.so double.c

Após a compilação será gerado o objeto libpgdouble.so que deve ser copiado para a pasta lib do PostgreSQL com o seguinte comando:

cp sqlmagazine.so /usr/local/pgsql/lib

3. Criação da função no banco com a mesma assinatura e tipos de dados equivalentes da função implementada em C

O comando a seguir cria no PostgreSQL a função double_me que contém um “ponteiro” para execução da função implementada e compilada nos passos anteriores. Vamos utilizar o banco de dados sqlmagazine criado nas edições anteriores para executar o comando de criação das funções e selects apresentados nesse artigo.

CREATE FUNCTION double_me(int4) RETURNS int4 AS '/usr/local/pgsql/lib/libpgdouble.so' LANGUAGE 'c';

Observe que: i) o tipo de dados do parâmetro da função no PostgreSQL é int4, que é o tipo de dados equivalente ao int no C e, ii) no corpo da função há apenas uma linha “apontando” para o objeto SO criado no passo 2. O último modificador do comando, LANGUAGE ‘C’, indica que a função será implementada utilizando a linguagem C. A tabela 1 mostra os tipos de dados em C e seus equivalentes no PostgreSQL bem como os includes que devem ser adicionados ao fonte em C para sua utilização.

Para executar a função implementada basta executar o seguinte comando no banco de dados sqlmagazine, como qualquer outra função no PostgreSQL:

SELECT double_me(5);
      
     double_me 
     -----------
             10
     
    

Nota: Não é aconselhável efetuar testes de desenvolvimento de funções em C utilizando a mesma máquina de produção pois caso a função gere algum erro, o processo do PostgreSQL pode ser parado em virtude da violação de segurança.

Tabela 1 – Tipos de dados equivalentes entre o C e o PostgreSQL

Utilizando recursos do PostgreSQL para implementar funções em C

É possível escrever função na forma tradicional como visto na seção anterior ou usando os recursos do header postgres.h, que inclui os tipos de dados do PostgreSQL, funções para definição de parâmetros, etc, tornando o código mais estável uma vez que sua execução é efetuada no mesmo processo do kernel do banco de dados. O exemplo da listagem 2 mostra uma implementação genérica onde a função recebe dois parâmetros e retorna um string com os mesmos concatenados e separados por um espaço.

     01 #include "postgres.h"
     02 #include 
     03 #include "fmgr.h"
     04 
     05 PG_FUNCTION_INFO_V1(sql_magazine);
     06 
     07 Datum sql_magazine(PG_FUNCTION_ARGS)
     08 {
     09  text  *arg1 = PG_GETARG_TEXT_P(0);
     10  text  *arg2 = PG_GETARG_TEXT_P(1);
     11
     12  int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
     13  text *new_text = (text *) palloc(new_text_size);
     14 
     15  VARATT_SIZEP(new_text) = new_text_size;
     16  memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1)-VARHDRSZ);
     17  memcpy(VARDATA(new_text) + (VARSIZE(arg1)-VARHDRSZ),
     18         VARDATA(arg2), VARSIZE(arg2)-VARHDRSZ);
     19  
     20  PG_RETURN_TEXT_P(new_text);
     21 }

Listagem 2 – Implementação de funções em c utilizando o header postgres.h

Observando a listagem 2, temos:

• Linha 5: Esta linha diz ao PostgreSQL que estaremos usando a versão 1 (a mais recente) para implementar funções em C. Entre () está o nome da função.
• Linha 7: Esta é uma linha padrão no formato Datum nome_funcao(PG_FUNCTION_ARGS)
• Linhas 9 e 10: Aqui são criadas variáveis para manipular os valores passados como parâmetros. text é um tipo de dados proveniente do postgres.h e PG_GETARG_TEXT_P(0) retorna o valor do primeiro parâmetro da função definida no PostgreSQL (CREATE FUNCTION) no formato TEXT. Se esse parâmetro fosse do tipo int32, utilizaríamos PG_GETARG_INT32(1). Na maioria das vezes, para acessar o parâmetro da função, utilizaremos a sintaxe PG_GETARG_TIPODEDADOS(índice) ou PG_GETARG_TIPODEDADOS_P(índice) para tipos text e point.
• Linha 20: Nesta linha temos a atribuição do retorno da função de acordo com seu tipo de dados. A função de retorno tem sempre o formato PG_RETURN_TIPODEDADO(valor) ou PG_RETURN_TIPODEDADO_P(valor) para tipos text e point.

Salve o arquivo da listagem 2 como sqlmagazine.c e compile com o seguinte comando:

gcc -fpic -I ../../src/include/ -c sqlmagazine.c

Este comando irá compilar e gerar um arquivo “.o”. Agora iremos transformá-lo em .so:

gcc -shared -o sqlmagazine.so sqlmagazine.o

Copie o arquivo SO gerado para a pasta lib do PostgreSQL com o comando

#cp sqlmagazine.so /usr/local/pgsql/lib

E por fim crie a função no banco de dados sqlmagazine com o seguinte comando:

CREATE FUNCTION sql_magazine(text,text) RETURNS text AS '/usr/local/pgsql/lib/sqlmagazine.so' LANGUAGE 'c' WITH (isStrict);

Para verificar o resultado da função criada, execute o comando a seguir:

      select sql_magazine('SQL ', ' Magazine');                                                                                       
      sql_magazine  
     ---------------
      SQL  Magazine
     (1 row)
    

Como executar um código SQL nas funções implementadas em C

O “acesso” e execução de comandos SQL no PostgreSQL a partir de funções implementadas em C é efetuado através de funções contidas no header spi.h. Essas funções possibilitam, além da execução de comandos, a manipulação e acesso aos registros retornados por uma determinada consulta, o controle da quantidade de registros, verificação de erros gerados pela execução dos comandos, entre outros.

O exemplo da listagem 3 implementa uma função que, a partir de um valor inteiro passado como parâmetro, busca o registro da tabela sql_magazine cujo identificador equivale ao parâmetro passado e retorna a mensagem equivalente ao valor ou uma mensagem de erro do processamento.

     01 #include "postgres.h"
     02 #include 
     03 #include "fmgr.h"
     04 #include "executor/spi.h"
     05
     06 PG_FUNCTION_INFO_V1(sql_magazine);
     07
     08 Datum
     09 sql_magazine(PG_FUNCTION_ARGS)
     10 {
     11     text *arg1 = PG_GETARG_TEXT_P(0);
     12     text *new_text;
     13     char query[150];
     14     int ret,proc;
     15     int new_text_size = 8024;
     16     SPI_connect();
     17 
     18     sprintf(query,"select mensagem from sql_magazine where ano = '%s'",VARDATA(arg1));
     19     ret = SPI_exec(query, 0);
     20    
     21     if (ret != SPI_OK_SELECT) { 
     22        elog(ERROR, "SPI_exec, XIII AI MEU DEUS, ERRO!!!.");
     23        PG_RETURN_NULL(); 
     24     }
     25 
     26   proc = SPI_processed;
     27    
     28     if (proc <= 0) {
     29        elog(ERROR, "SPI_exec, sem retorno de registros!");
     30        PG_RETURN_NULL();
     31     }
     32     new_text = (text *) palloc(new_text_size);
     33        
     34     strcpy(&VARDATA(new_text)[strlen(VARDATA(new_text))],
     35     DatumGetCString(SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1)));
     36
     37 SPI_finish();
     38 
     39 PG_RETURN_TEXT_P(new_text);
     40
     41     pfree(new_text);
     42 }
    

Listagem 3 – Executando comandos SQL em funções C

Observando a listagem 3, temos:

• Linha 11: Cria uma variável e inicializa com o valor do parâmetro passado.
• Linha 16: “Abre” a conexão com a seção que chamou a execução da função em C.
• Linha 18: “Monta” a string com a frase SQL a ser executada e armazena na variável query.
• Linha 19: Executa a frase SQL através da função SPI_EXEC e armazena o retorno deste processamento na variável ret. A função SPI_EXEC possui dois parâmetros: o primeiro equivale ao comando a ser executado e o segundo a quantidade de registros afetados ou retornados pelo comando - em nosso exemplo utilizamos ‘zero’ pois vamos processar todos os registros retornados.
• Linha 26: Armazena na variável proc o número de registros processados ou retornados pelo comando executado, através da função SPI_PROCESSED.
• Linhas 34 e 35: Acessam o valor da primeira coluna vals[0] do registro corrente retornado pelo SPI_EXEC, através do SPI_tuptable.
• Linha 37: “Fecha” a conexão aberta na linha 11.

Os comandos a seguir compilam o código da listagem 3 e movem o objeto C para a pasta lib do PostgreSQL.

gcc -fpic -I ../../src/include/ -c sqlmagazine.c
     gcc -shared -o sqlmagazine.so sqlmagazine.o
     mv sqlmagazine.so /usr/local/pgsql/lib/
    

Criando a função no PostgreSQL no banco de dados sqlmagazine:

CREATE FUNCTION sql_magazine(text) RETURNS text AS '/usr/local/pgsql/lib/sqlmagazine.so' LANGUAGE 'c' WITH (isStrict);

Os comandos a seguir criam a tabela utilizada na listagem 3 e efetuam a inserção de um registro na mesma:

CREATE TABLE SQL_MAGAZINE(mensagem text, ano char(4));                                                                         
     INSERT INTO SQL_MAGAZINE(mensagem,ano) VALUES('Feliz 2004!!!', '2004');               
    

Vamos verificar dois possíveis resultados da execução da função: O primeiro com um identificador não cadastrado e o segundo com um identificador cadastrado pela listagem anterior:

select sql_magazine('2222');
     ERROR:  SPI_exec, sem retorno de registros!
      
     select sql_magazine('2004');                                                                                                    
     sql_magazine  
     ----------------
     Feliz 2004!!!
     (1 row)
    

Conclusão

Esse artigo mostrou de forma introdutória a utilização do C para implementação de funções no PostgreSQL. É claro que sua utilização requer conhecimento e experiência na linguagem mas, o horizonte que a mesma abre ao desenvolvedor compensa o esforço. Sem contar com os ganhos relacionados à segurança e velocidade de processamento.