【转】Openerp 使用 Postgresql 存储过程和视图
-
OpenERP 使用 postgresql 存储过程和试图，步骤如下：
转自本人博客 ： http://buke.github.io/blog/2013/04/22/openerp-postgresql-stored-procedure-and-view/
STEP1: 在模块的 init 函数中定义存储过程<br /> def init(self, cr):<br /> ''' create stored procedure '''<br /> cr.execute("""CREATE OR REPLACE FUNCTION fn_fi_report_childs(int)<br /> RETURNS TABLE(id int) AS $$<br /> WITH RECURSIVE t AS (<br /> SELECT id,parent_id FROM fi_report WHERE id = $1<br /> UNION ALL<br /> SELECT fi_report.id, fi_report.parent_id FROM fi_report, t WHERE fi_report.parent_id = t.id<br /> )<br /> SELECT id FROM t;<br /> $$ LANGUAGE SQL<br /> """)<br /><br />
或者定义视图<br /> def init(self, cr):<br /> tools.drop_view_if_exists(cr, 'analytic_entries_report')<br /> cr.execute("""<br /> create or replace view analytic_entries_report as (<br /> select<br /> min(a.id) as id,<br /> count(distinct a.id) as nbr,<br /> a.date as date,<br /> to_char(a.date, 'YYYY') as year,<br /> to_char(a.date, 'MM') as month,<br /> to_char(a.date, 'YYYY-MM-DD') as day,<br /> a.user_id as user_id,<br /> a.name as name,<br /> analytic.partner_id as partner_id,<br /> a.company_id as company_id,<br /> a.currency_id as currency_id,<br /> a.account_id as account_id,<br /> a.general_account_id as general_account_id,<br /> a.journal_id as journal_id,<br /> a.move_id as move_id,<br /> a.product_id as product_id,<br /> a.product_uom_id as product_uom_id,<br /> sum(a.amount) as amount,<br /> sum(a.unit_amount) as unit_amount<br /> from<br /> account_analytic_line a, account_analytic_account analytic<br /> where analytic.id = a.account_id<br /> group by<br /> a.date, a.user_id,a.name,analytic.partner_id,a.company_id,a.currency_id,<br /> a.account_id,a.general_account_id,a.journal_id,<br /> a.move_id,a.product_id,a.product_uom_id<br /> )<br /> """)<br /><br />
STEP2: 在模块的函数中使用存储过程<br /> def get_amount(self,cr,uid,id,period_id,context=None):<br /> cr.execute('SELECT * FROM fn_fi_report_childs(%s)', (id,))<br /><br />
而视图的话，则如普通的表一样使用。
STEP3: 完成！