[Reprinted] Using PostgreSQL stored procedures and views in openerp

Original article address: Use PostgreSQL stored procedures and views in openerp

 

The steps for openerp to use PostgreSQL stored procedures and attempts are as follows:

Step 1: Define the stored procedure in the module's init Function

DefInit (self, Cr ):'''Create Stored Procedure'''Cr.exe cute ("""Create or replace function fn_fi_report_childs (INT) returns table (id int) as $ with recursive T as (select ID, parent_id from fi_report where id = $1 Union all select fi_report.id, fi_report.parent_id from fi_report, t where fi_report.parent_id = T. ID) Select ID from t; $ language SQL""")
 

Or define the view

     Def Init (self, Cr): Tools. drop_view_if_exists (Cr,  '  Analytic_entries_report  '  ) Cr.exe cute (  """ Create or replace view analytic_entries_report as (select Min (. ID) as ID, count (distinct. ID) as NBR,. date as date, to_char (. date, 'yyyy') as year, to_char (. date, 'mm') as month, to_char (. date, 'yyyy-MM-DD ') as day,. user_id as user_id,. name as name, analytic. partner_id as partner_id,. company_id as company_id,. currency_id as currency_id,. account_id as account_id,. general_account_id as general_account_id,. journal_id as journal_id,. move_id as move_id,. product_id as product_id,. product_uom_id as product_uom_id, sum (. amount) as amount, sum (. unit_amount) as unit_amount from account_analytic_line A, account_analytic_account analytic where analytic. id =. account_id group by. date,. user_id,. name, analytic. partner_id,. company_id,. currency_id,. account_id,. general_account_id,. journal_id,. move_id,. product_id,. product_uom_id)""" )

Step 2: Use stored procedures in module functions

 
DefGet_amount (self, Cr, uid, ID, period_id, context =None): cr.exe cute ('Select * From fn_fi_report_childs (% s)', (ID ,))
 

The view is used like a normal table.

Step 3: complete!