我有一个函数,它使用以下代码(full function code over here)将触发器添加到指定的表中:
event.listen(primary_table, 'after_create', DDL('''
CREATE FUNCTION {primary_table_name}_validate() RETURNS TRIGGER AS $$
DECLARE
target RECORD;
BEGIN
IF (NEW.{rhs} IS NOT NULL) THEN
SELECT {parentcol} INTO target FROM {child_table_name} WHERE {child_id_column} = NEW.{rhs};
IF (target.{parentcol} != NEW.{lhs}) THEN
RAISE foreign_key_violation USING MESSAGE = 'The target is not affiliated with this parent';
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER {primary_table_name}_trigger BEFORE INSERT OR UPDATE
ON {primary_table_name}
FOR EACH ROW EXECUTE PROCEDURE {primary_table_name}_validate();
'''.format(
primary_table_name=primary_table_name,
parentcol=parentcol,
child_table_name=child_table_name,
child_id_column=child_id_columns[0],
lhs=parent_table_name + '_' + parent_id_columns[0],
rhs=child_table_name + '_' + child_id_columns[0],
)).execute_if(dialect='postgresql'))
event.listen(primary_table, 'before_drop', DDL('''
DROP TRIGGER {primary_table_name}_trigger ON {primary_table_name};
DROP FUNCTION {primary_table_name}_validate();
'''.format(primary_table_name=primary_table_name)).execute_if(dialect='postgresql'))
我不想在SQL中嵌入原始字符串,因为它会阻塞像user
(使用PostgreSQL)这样的名称。我不能使用SQLAlchemy的bindparam
,因为它们是表和列名,而不是数据。SQLAlchemy offers a ^{
回答我自己的问题,
DDL
接受应用于SQL语句的context
字典。这是修订后的法典:相关问题 更多 >
编程相关推荐