Extend Func using Django's template engine

[adonig]

Hi @Opus10! I'm not sure whether this is something that might be interesting to include or mention in the documentation:

import pgtrigger
from django.template import Context, Template


class TemplateFunc(pgtrigger.Func):
    def render(self, model):
        fields = {field.name: field for field in model._meta.fields}
        columns = {field.name: field.column for field in model._meta.fields}
        template = Template(self.func)
        context = Context({"meta": model._meta, "fields": fields, "columns": columns})
        return template.render(context)

It allows you to use Django's template engine to write trigger functions. I had the following use case:

   class Meta:
        triggers = [
            pgtrigger.Trigger(
                name="product_upsert",
                operation=pgtrigger.Insert,
                timing=pgtrigger.Immediate,
                when=pgtrigger.After,
                func=TemplateFunc(
                    """
                        IF EXISTS (
                            SELECT *
                            FROM {{ meta.db_table }}
                            WHERE {{ columns.gtin }}=NEW.{{ columns.gtin }} OR (
                                {{ columns.category }}=NEW.{{ columns.category }} AND
                                {{ columns.subcategory }}=NEW.{{ columns.subcategory }} AND
                                {{ columns.vendor }}=NEW.{{ columns.vendor }} AND
                                {{ columns.model }}=NEW.{{ columns.model }}
                            )
                        ) THEN
                            UPDATE {{ meta.db_table }} SET
                            {% for column in columns %}
                            {% if column != "id" and column != "created" %}
                                {{ column }}=NEW.{{ column }}{% if not forloop.last %},{% endif %}
                            {% endif %}
                            {% endfor %}
                            WHERE {{ columns.gtin }}=NEW.{{ columns.gtin }} OR (
                                {{ columns.category }}=NEW.{{ columns.category }} AND
                                {{ columns.subcategory }}=NEW.{{ columns.subcategory }} AND
                                {{ columns.vendor }}=NEW.{{ columns.vendor }} AND
                                {{ columns.model }}=NEW.{{ columns.model }}
                            );
                            RETURN NULL;
                        ELSE
                            RETURN NEW;
                        END IF;
                    """
                ),
            ),
        ]

[adonig]

Here's a typed version that allows to specify an extra context:

class TemplateFunc(pgtrigger.Func):
    def __init__(self, func: str, extra_context: dict[str, Any] | None = None) -> None:
        super().__init__(func)
        self.extra_context = extra_context or {}

    def render(self, model: type[models.Model]) -> str:
        fields = {field.name: field for field in model._meta.fields}
        columns = {field.name: field.column for field in model._meta.fields}
        template = Template(self.func)
        context = Context({**{"meta": model._meta, "fields": fields, "columns": columns}, **self.extra_context})
        return template.render(context)

[wesleykendall]

@adonig I really like this suggestion!

I'm happy to accept this as a PR to django-pgtrigger as is. I can also open a PR and add you as a Co-authored-by in the comment.

One suggestion - I wonder if there's a way to grab these variables for other models. It's common to write a trigger across a few tables.

Could also throw in a basic template tag for people that want to write functions over other models:

"""
{% load meta from pgtrigger %}

{% meta "app.MyModel" as my_model_meta %}

SELECT * from {{ my_model_meta.db_name }}
"""

Not sure if that's totally correct, but I hope it makes sense.

[adonig]

@wesleykendall I'm glad it turns out to become useful. The template tag sounds like a good idea. It might look like this:

from django import template
from django.apps import apps
from django.db.models.options import Options

register = template.Library()

@register.simple_tag(name="meta")
def meta(model_name: str) -> Options:    
    model = apps.get_model(model_name)
    return model._meta

[wesleykendall]

Revisiting this discussion since there were two PRs (#149, #150) that both attempted to work around trigger definition limitations. CC @joetsoi and @christophehenry for visibility.

To summarize:

• Allow use of Django's template engine, which will always have the triggered model's metadata
• Template tags that can easily obtain metadata/cols of other models
• The ability to reference a file stored in templates/

Let me know if anything else might be useful here, and I will plan on getting this into pgtrigger