👉🏼 🤲🏻 👨🏽‍🏭 PostgreSQL recipes: mustach template engine 🦔 🎄 👩‍⚖️

To prepare the mustach template engine, we need postgres and mustach . You can also use the finished image .

Why do we need a template engine in the database? Well, firstly, if the template engine is in the database, then the templates themselves should also be in the database. And why do you need to store templates in the database? Yes, because templates, like data, can also be time dependent. For example, suppose there are accounts in the database (this is data). Obviously, they depend on time: this month the amount is one, in the next - another, then - the third, etc. But the invoice template can also depend on the time: this year one, and the next another (as was the case with the introduction of 20%). Therefore, it is more convenient to store the templates themselves in the database. Well, the template engine in the database is convenient in that you can template it right there in the database, then (right there in the database) convert it to pdf and (right there in the database) send it by email . And all this can be done asynchronously using the scheduler.

The code is not too big, so I post it all (with comments)

#include <postgres.h> //   .

#include <catalog/pg_type.h> //  
extern text *cstring_to_text(const char *s); //  
extern text *cstring_to_text_with_len(const char *s, int len); //  
extern char *text_to_cstring(const text *t); //  , 
extern void text_to_cstring_buffer(const text *src, char *dst, size_t dst_len); //   
#define CStringGetTextDatum(s) PointerGetDatum(cstring_to_text(s)) //   
#define TextDatumGetCString(d) text_to_cstring((text *) DatumGetPointer(d)) //   json_object
#include <mustach/mustach-json-c.h> //    

#define EXTENSION(function) Datum (function)(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(function); Datum (function)(PG_FUNCTION_ARGS) //     

PG_MODULE_MAGIC; //   

EXTENSION(json2mustach) { //   
    char *file; //   
    char *json; //   
    char *output_data; //   
    char *template; // 
    enum json_tokener_error error; //     
    FILE *out; //  
    size_t output_len; //    
    struct json_object *object; //  
    text *output; // 
    if (PG_ARGISNULL(0)) ereport(ERROR, (errmsg("json is null!"))); //      NULL
    if (PG_ARGISNULL(1)) ereport(ERROR, (errmsg("template is null!"))); //   - 
    json = TextDatumGetCString(PG_GETARG_DATUM(0)); //  C-   
    template = TextDatumGetCString(PG_GETARG_DATUM(1)); //   
    if (!(object = json_tokener_parse_verbose(json, &error))) ereport(ERROR, (errmsg("!json_tokener_parse and %s", json_tokener_error_desc(error)))); //          
    switch (PG_NARGS()) { //     
        case 2: if (!(out = open_memstream(&output_data, &output_len))) ereport(ERROR, (errmsg("!open_memstream"))); break; //         
        case 3: //   
            if (PG_ARGISNULL(2)) ereport(ERROR, (errmsg("file is null!"))); //      NULL
            file = TextDatumGetCString(PG_GETARG_DATUM(2)); //  C-   
            if (!(out = fopen(file, "wb"))) ereport(ERROR, (errmsg("!fopen"))); //          
            pfree(file); //  
            break; // 
        default: ereport(ERROR, (errmsg("expect be 2 or 3 args"))); //        
    }
    if (fmustach_json_c(template, object, out)) ereport(ERROR, (errmsg("fmustach_json_c"))); //        
    pfree(json); //  
    pfree(template); //  
    if (!json_object_put(object)) ereport(ERROR, (errmsg("!json_object_put"))); //   
    switch (PG_NARGS()) { //     
        case 2: //   
            fclose(out); //    
            output = cstring_to_text_with_len(output_data, output_len); //   
            free(output_data); //  
            PG_RETURN_TEXT_P(output); //  
            break; // 
        case 3: PG_RETURN_BOOL(true); break; //      
        default: ereport(ERROR, (errmsg("expect be 2 or 3 args"))); //        
    }
}

We use it like this: first create an extension

create extension pg_mustach

and then template into memory

select json2mustach('{"name":""}', ', {{name}}!')

or to file

select json2mustach('{"name":""}', ', {{name}}!', 'file_name')

PostgreSQL recipes: mustach template engine

More articles: