Funções em C no database PostgreSQL

O bacana de ministrar treinamentos sobre o database PostgreSQL é que aprendemos sob demanda. Sob a demanda dos alunos. O verdadeiro professor não é aquele que simplesmente passa seu conhecimento, mas aquele que também instiga o aluno a buscar soluções criativas para seus problemas.
O Carlos Mayer, das Lojas MM – Mercadomóveis de Curitiba, um dos alunos do treinamento de PostgreSQL que ministrei por lá, me escreveu, recentemente, com uma questão no mínimo interessante: ele precisava de dentro de uma function PG processar arquivos no sistema de arquivos do SO. Mas de forma a ter total controle do que está sendo executado.


Uma das caracteristicas de segurança do PostgreSQL é não permitir que se tenha acesso diretamente ao sistema
de arquivos, mas há maneiras de se contornar isso, em casos extremos e específicos onde necessita-se de
total controle, programando no nível mais baixo possível.
Parece coisa de outro mundo, mas obviamente não é uma vez que o PostgreSQL conta com a possibilidade de embarcar
para dentro de sua stack, funções em linguagem C, escritas por você mesmo, meu caro programador.
ATENÇÃO: Programar em C profissionalmente, além de perigoso é altamente aditivo.
Recomenda-se acompanhamento de um profissional experiente. ; )
No exemplo, meramente didático, que mostrarei abaixo aproveitamos para criar uma biblioteca em C,
que podemos colocar várias funções e após compilar esta biblioteca e copiá-la para a pasta correta no Postgres,
apenas necessitamos vincular a função já disponível na library para ser vista pelo PostgreSQL.
Abaixo o arquivo em C, que nada mais é que a biblioteca com as funções:

#include "postgres.h"
#include
#include
#include
#include "catalog/pg_type.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "postmaster/syslogger.h"
#include "storage/fd.h"
#include "utils/builtins.h"
#include "utils/datetime.h"
#include "fmgr.h"
#include "miscadmin.h"
#ifdef PG_MODULE_MAGIC
 PG_MODULE_MAGIC;
#endif
typedef struct
{
        char    *location;
        DIR     *dirdesc;
} directory_fctx;
/*-----------------------
 *  * some helper functions
 *   */
/* Auxiliary Functions should go first */
/*
 *  * check for superuser, bark if not.
 *  */
static void
requireSuperuser(void)
{
	if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), (errmsg("only superuser may access this"))));
}
Datum bytea_size(PG_FUNCTION_ARGS);
Datum hello_world(PG_FUNCTION_ARGS);
Datum vnx_pg_logdir_ls(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(bytea_size);
PG_FUNCTION_INFO_V1(hello_world);
PG_FUNCTION_INFO_V1(vnx_pg_logdir_ls);
Datum bytea_size(PG_FUNCTION_ARGS)
{
     bytea *data = PG_GETARG_BYTEA_P(0);
     unsigned char *ptr = (unsigned char *) VARDATA(data);
     int32 tcount = 0, i;
     // count characters
     for (i = VARSIZE(data) - VARHDRSZ; i; i--) {
         if (!i%1000) CHECK_FOR_INTERRUPTS();
	        unsigned char c = *ptr++; // get the char
         tcount++;
     }
     PG_RETURN_INT32(tcount);
}
Datum hello_word(PG_FUNCTION_ARGS)
{
}
Datum
vnx_pg_logdir_ls(PG_FUNCTION_ARGS)
{
        FuncCallContext *funcctx;
        struct dirent *de;
        directory_fctx *fctx;
        if (!superuser())
                ereport(ERROR,
                                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                                 (errmsg("only superuser can list the log directory"))));
        if (strcmp(Log_filename, "postgresql-%Y-%m-%d_%H%M%S.log") != 0)
                ereport(ERROR,
                                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                                 (errmsg("the log_filename parameter must equal 'postgresql-%%Y-%%m-%%d_%%H%%M%%S.log'"))));
        if (SRF_IS_FIRSTCALL())
        {
                MemoryContext oldcontext;
                TupleDesc       tupdesc;
                funcctx = SRF_FIRSTCALL_INIT();
                oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
                fctx = palloc(sizeof(directory_fctx));
                tupdesc = CreateTemplateTupleDesc(2, false);
                TupleDescInitEntry(tupdesc, (AttrNumber) 1, "starttime",
                                                   TIMESTAMPOID, -1, 0);
                TupleDescInitEntry(tupdesc, (AttrNumber) 2, "filename",
                                                   TEXTOID, -1, 0);
                funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc);
                fctx->location = pstrdup(Log_directory);
                fctx->dirdesc = AllocateDir(fctx->location);
                if (!fctx->dirdesc)
                        ereport(ERROR,
                                        (errcode_for_file_access(),
                                         errmsg("could not read directory "%s": %m",
                                                        fctx->location)));
                funcctx->user_fctx = fctx;
                MemoryContextSwitchTo(oldcontext);
        }
        funcctx = SRF_PERCALL_SETUP();
        fctx = (directory_fctx *) funcctx->user_fctx;
        while ((de = ReadDir(fctx->dirdesc, fctx->location)) != NULL)
        {
                char       *values[2];
                HeapTuple       tuple;
                char            timestampbuf[32];
                char       *field[MAXDATEFIELDS];
                char            lowstr[MAXDATELEN + 1];
                int                     dtype;
                int                     nf,
                                        ftype[MAXDATEFIELDS];
                fsec_t          fsec;
                int                     tz = 0;
                struct pg_tm date;
                /*
 		** Default format: postgresql-YYYY-MM-DD_HHMMSS.log
 		**/
                if (strlen(de->d_name) != 32
                        || strncmp(de->d_name, "postgresql-", 11) != 0
                        || de->d_name[21] != '_'
                        || strcmp(de->d_name + 28, ".log") != 0)
                        continue;
                /* extract timestamp portion of filename */
                strcpy(timestampbuf, de->d_name + 11);
                timestampbuf[17] = '';
                /* parse and decode expected timestamp to verify it's OK format */
                if (ParseDateTime(timestampbuf, lowstr, MAXDATELEN, field, ftype, MAXDATEFIELDS, &nf))
                        continue;
                if (DecodeDateTime(field, ftype, nf, &dtype, &date, &fsec, &tz))
                        continue;
                /* Seems the timestamp is OK; prepare and return tuple */
                values[0] = timestampbuf;
                values[1] = palloc(strlen(fctx->location) + strlen(de->d_name) + 2);
                sprintf(values[1], "%s/%s", fctx->location, de->d_name);
                tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);
                SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
        }
        FreeDir(fctx->dirdesc);
        SRF_RETURN_DONE(funcctx);
}

 
Abaixo o Makefile, que nos permitirá gerar a biblioteca compilada com o comando ‘make’.

MODULES = myLilLibrary
PG_CONFIG = pg_config
PGXS = $(shell $(PG_CONFIG) --pgxs)
INCLUDEDIR = $(shell $(PG_CONFIG) --includedir-server)
include $(PGXS)
myLilLibrary.so: myLilLibrary.o
	cc -shared -o myLilLibrary.so myLilLibrary.o
myLilLibrary.o: myLilLibrary.c
	cc -o myLilLibrary.o -c myLilLibrary.c $(CFLAGS) -I$(INCLUDEDIR)

E então podemos associar a função ao banco conectado com o seguinte comando

-- Associa a função da biblioteca em C, com o PostgreSQL
-- permitindo que o mesmo execute a função em tempo de execução PL/SQL
--
CREATE FUNCTION vnx_pg_logdir_ls() RETURNS TABLE (T timestamp, nome TEXT)
  AS 'myLilLibrary', 'vnx_pg_logdir_ls'
  LANGUAGE C STRICT;
-- Chama a função
--
SELECT * FROM vnx_pg_logdir_ls();

Vinícius Schmidt é analista/consultor em sistemas, infra, banco de dados e segurança da informação.
Especialista em OpenSource desde 1999, atua com PostgreSQL (dentre outros OSS) desde 2000.
 
Colaboraram: Matheus Oliveira (artigo), partes do exemplo em C por Andreas Pflug da PGDG, criador da contrib/adminpack.