id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
31622	Support CREATE CONSTRAINT TRIGGER constraints in postgres.	Jerome Leclanche		"Context: My ultimate goal is to be able to define a function (with its body) and make it a table constraint in Django. Sqlalchemy has no support for either of those, FWIW.

In that light, I wanted to start by implementing constraint triggers, and for now just have them reference pre-existing functions implemented separately, using `Func(function=""function_name"")`..

I have some initial working code here: https://github.com/jleclanche/django/commit/postgres-trigger-constraints

Tested that code in my app with creating/dropping constraints, constraint trigger equality, deferrability (using Django 3.1's new Deferrable object from the new deferrable index impl). I also tested conditions using RawSQL(), couldn't figure out a way of referencing my conditions directly otherwise. 

I'd like to help upstream this but I don't have a lot of free time. I could use the help.

Example usage from my app:

{{{
class Account(models.Model):
	class Meta:
		constraints = [
			ConstraintTrigger(
				name=""update_full_account_codes_trigger"",
				events=[TriggerEvent.INSERT, TriggerEvent.UPDATE, TriggerEvent.DELETE],
				condition=RawSQL(""pg_trigger_depth() < %s"", (1,)),
				function=models.Func(function=""update_full_account_codes""),
			),
			ConstraintTrigger(
				name=""check_account_type_trigger"",
				events=[TriggerEvent.INSERT, TriggerEvent.UPDATE],
				condition=RawSQL(""pg_trigger_depth() < %s"", (1,)),
				function=models.Func(function=""check_account_type""),
			),
		]
}}}

Generates the following migration:

{{{
    operations = [
        migrations.AddConstraint(
            model_name='account',
            constraint=financica.contrib.constraints.ConstraintTrigger(condition=django.db.models.expressions.RawSQL('pg_trigger_depth() < %s', (1,)), events=('INSERT', 'UPDATE', 'DELETE'), function=django.db.models.expressions.Func(function='update_full_account_codes'), name='update_full_account_codes_trigger'),
        ),
        migrations.AddConstraint(
            model_name='account',
            constraint=financica.contrib.constraints.ConstraintTrigger(condition=django.db.models.expressions.RawSQL('pg_trigger_depth() < %s', (1,)), events=('INSERT', 'UPDATE'), function=django.db.models.expressions.Func(function='check_account_type'), name='check_account_type_trigger'),
        ),
    ]
}}}

References:

- CREATE CONSTRAINT TRIGGER syntax: https://www.postgresql.org/docs/9.0/sql-createconstraint.html
- CREATE CONSTRAINT generic syntax: https://www.postgresql.org/docs/12/sql-createtrigger.html"	New feature	closed	contrib.postgres	3.1	Normal	wontfix	constraints, postgres, triggers	Tom Forbes	Unreviewed	0	0	0	0	0	0