sqlalchemy/postgresql:数据库列计算的默认值

本文介绍了sqlalchemy/postgresql:数据库列计算的默认值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个事务表，其中有tenant_id和transaction_id列(它们构成唯一的复合索引).对于插入操作，transaction_id必须增加1，但对于给定的tenant_id.因此，使用sqlalchemy框架，我手动找到了tenant_id的最大transaction_id:

I have a transaction table, which has tenant_id and transaction_id columns (they make up a unique composite index). For insert operation, transaction_id must be incremented by 1, but for given tenant_id. So, using sqlalchemy framework, I manually find max transaction_id for tenant_id:

res = db.session.query(func.max(my_tran.transaction_id).label('last_id')) \ .filter_by(tenant_id=tenant_id).one() if res.last_id: my_tran.transaction_id = res.last_id else: my_tran.transaction_id = 1

我想做的是将模型类的逻辑定义为服务器默认值:

What I'd like to do instead is define the logic for my model class as server default:

class MyTran(db.Model): __tablename__ = 'my_tran' id = db.Column(db.Integer, primary_key=True) tenant_id = db.Column(db.Integer, db.ForeignKey('tenant.id'), nullable=False) transaction_id = db.Column(db.Integer, nullable=False, \ server_default='compute last id for tenant_id + 1')

我想我需要创建一个触发器(如何?)，但是不知道如何链接到我的模型类.

I guess I need to create a trigger (how?), but don't know how to link to my model class.

推荐答案

我找到了答案这里.详细信息如下(使用sqlalchemy):

I found my answer here. Details are below (using sqlalchemy):

create_fn_my_tran_set_num = DDL( ''' CREATE OR REPLACE FUNCTION fn_my_tran_set_num() RETURNS TRIGGER AS $$ DECLARE last_transaction_id INTEGER; BEGIN last_transaction_id := MAX(transaction_id) FROM my_tran WHERE tenant_id = NEW.tenant_id; IF last_transaction_id IS NULL THEN NEW.transaction_id := 1; ELSE NEW.transaction_id := last_transaction_id + 1; END IF; RETURN NEW; END$$ LANGUAGE plpgsql ''') event.listen(MyTran.__table__, 'after_create', create_fn_my_tran_set_trx_id) create_tg_my_tran_set_num = DDL( ''' CREATE TRIGGER tg_my_tran_set_num BEFORE INSERT ON my_tran FOR EACH ROW EXECUTE PROCEDURE fn_my_tran_set_num(); ''') event.listen(MyTran.__table__, 'after_create', create_tg_my_tran_set_trx_id)