Odoo中文社区可以通过以下两个域名访问:shine-it.net , odoo.net.cn

由于系统升迁的原因,本论坛部分较早期的内容存在格式和链接损坏失效的问题,并非本论坛系统本身的缺陷,望谅解

本社区没有维护任何QQ群讨论组,任何与本社区同名的QQ群讨论组的言论与本社区无关!

开发人员可以登录gitter讨论组: http://gitter.im/odoo-china/Talk, 需要github账号

如果您登录系统碰到问题,请在微信公众号留言:

【转】Openerp 使用 Postgresql 存储过程和视图



  • OpenERP 使用 postgresql 存储过程和试图,步骤如下:
    转自本人博客 : http://buke.github.io/blog/2013/04/22/openerp-postgresql-stored-procedure-and-view/

    STEP1: 在模块的 init 函数中定义存储过程

    <br />&nbsp; &nbsp; def init(self, cr):<br />&nbsp; &nbsp; &nbsp; &nbsp; &#039;&#039;&#039; create stored procedure &#039;&#039;&#039;<br />&nbsp; &nbsp; &nbsp; &nbsp; cr.execute(&quot;&quot;&quot;CREATE OR REPLACE FUNCTION fn_fi_report_childs(int)<br />&nbsp; &nbsp; &nbsp; &nbsp; RETURNS TABLE(id int) AS $$<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WITH RECURSIVE t AS (<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT id,parent_id&nbsp; FROM fi_report WHERE id = $1<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UNION ALL<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT fi_report.id, fi_report.parent_id FROM fi_report, t WHERE fi_report.parent_id = t.id<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT id FROM t;<br />&nbsp; &nbsp; &nbsp; &nbsp; $$ LANGUAGE SQL<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &quot;&quot;&quot;)<br /><br />
    



    或者定义视图

    <br />&nbsp; &nbsp; def init(self, cr):<br />&nbsp; &nbsp; &nbsp; &nbsp; tools.drop_view_if_exists(cr, &#039;analytic_entries_report&#039;)<br />&nbsp; &nbsp; &nbsp; &nbsp; cr.execute(&quot;&quot;&quot;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; create or replace view analytic_entries_report as (<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  select<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  min(a.id) as id,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  count(distinct a.id) as nbr,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.date as date,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  to_char(a.date, &#039;YYYY&#039;) as year,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  to_char(a.date, &#039;MM&#039;) as month,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  to_char(a.date, &#039;YYYY-MM-DD&#039;) as day,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.user_id as user_id,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.name as name,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  analytic.partner_id as partner_id,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.company_id as company_id,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.currency_id as currency_id,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.account_id as account_id,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.general_account_id as general_account_id,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.journal_id as journal_id,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.move_id as move_id,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.product_id as product_id,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.product_uom_id as product_uom_id,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  sum(a.amount) as amount,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  sum(a.unit_amount) as unit_amount<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  from<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  account_analytic_line a, account_analytic_account analytic<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  where analytic.id = a.account_id<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  group by<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.date, a.user_id,a.name,analytic.partner_id,a.company_id,a.currency_id,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.account_id,a.general_account_id,a.journal_id,<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  a.move_id,a.product_id,a.product_uom_id<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )<br />&nbsp; &nbsp; &nbsp; &nbsp; &quot;&quot;&quot;)<br /><br />
    



    STEP2: 在模块的函数中使用存储过程

    <br />&nbsp; &nbsp; def get_amount(self,cr,uid,id,period_id,context=None):<br />&nbsp; &nbsp; &nbsp; &nbsp; cr.execute(&#039;SELECT * FROM fn_fi_report_childs(%s)&#039;, (id,))<br /><br />
    


    而视图的话,则如普通的表一样使用。

    STEP3: 完成!



  • 8错.... 学习.....


  • 管理员

    好东西!谢谢buke分享。


Log in to reply