title: Using a Stored Procedure in a View with Postgresql
body: <p>I have been trying to create a View where one of the column pending_amount gets its value as a result of a stored procedure execution. </p>
  
  <p>The stored procedure is <strong>pending_stock(int,int)</strong> and returns an integer. The view is created successfully but when i try to perform any query like select on the this view it takes ever to return a value.</p>
  
  <pre><code>CREATE OR REPLACE VIEW view_production_parts AS 
  SELECT p.part_id, p.min_amount, gp.part_num, gp.description
       , p.quantity_available
       , p.quantity_total - p.quantity_available AS quantity_alloc
       , p.quantity_total
       , (SELECT pending_stock(p.part_id, 0) AS pending_stock) AS pending_amount
       , p.production_run
       , CASE
             WHEN ppur.purchased_part_id IS NOT NULL THEN true
             ELSE false
         END AS is_purchased_part, ppur.purchased_part_id, p.store_move_type_id
       , gp.part_status_id, p.default_location
       , COALESCE(pwoh.part_work_order_hold_id, 0) AS part_work_order_hold_id
     FROM general_part gp
     JOIN part p ON gp.part_id = p.part_id
     LEFT JOIN purchased_part ppur ON ppur.part_id = p.part_id
     LEFT JOIN part_work_order_hold pwoh ON pwoh.part_id = p.part_id
    ORDER BY gp.part_num;
  </code></pre>
  
  <p>Can a stored procedure be used in a view? If used, Is my declaration correct?</p>
  
  <p>Find the <a href="http://explain.depesz.com/s/elr" rel="nofollow">result from of this query at explain.depesz.com</a>:</p>
  
  <pre><code>EXPLAIN ANALYZE SELECT count(*) FROM view_production_parts
  </code></pre>
  
  <p>I am using Postgres 8.4.<br>
  Function definition for <code>pending_stock(int,int)</code>:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION pending_stock(var_part_id integer
                                         , var_pattern_id integer)
    RETURNS integer AS
  $BODY$
  declare
      r record;
      var_qty_expected integer;
      var_qty_moved_to_stock integer;
      var_total_stock_moved_out integer;
      var_actual_qty integer;
  
  begin
  
  var_total_stock_moved_out := 0;
  var_qty_expected := 0;
  
     for r in
        select work_order_id,quantity_expected
        from view_work_orders
        where part_id = var_part_id and open = 'TRUE'
        and quantity_allocated is null and quantity_expected &gt;= quantity_actual
  
     loop
        var_qty_expected = var_qty_expected + r.quantity_expected;
  
        select sum(quantity) from view_work_order_move_parts_details
        where source_work_order_id = r.work_order_id
        and part_id = var_part_id into var_qty_moved_to_stock;
  
        if var_qty_moved_to_stock is null then
           var_qty_moved_to_stock = 0;
        end if;
  
        var_total_stock_moved_out = var_total_stock_moved_out
                                  + var_qty_moved_to_stock;
     end loop;
  
     var_actual_qty := var_qty_expected - var_total_stock_moved_out;
  
     if var_actual_qty &gt; 0 then
        return var_actual_qty;
     else
        return 0;
     end if;
  end;
  $BODY$
  LANGUAGE 'plpgsql' VOLATILE STRICT
  COST 100;
  ALTER FUNCTION pending_stock(integer, integer) OWNER TO postgres;
  </code></pre>