Using a Stored Procedure in a View with Postgresql

Question

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.

The stored procedure is pending_stock(int,int) 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.

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;

Can a stored procedure be used in a view? If used, Is my declaration correct?

Find the result from of this query at explain.depesz.com:

EXPLAIN ANALYZE SELECT count(*) FROM view_production_parts

I am using Postgres 8.4.
Function definition for pending_stock(int,int):

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 >= 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 > 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;

• Ram: @Erwin: I am relatively new to Stack overflow. SO my format might be way of course. You made the question more readable and thanks a lot. I have updated the end of the function declaration as well.

Answer 1

View

You don't need a subquery for the function call. And you can simplify some other minor details:

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
 , pending_stock(gp.part_id, 0) AS pending_amount
 , p.production_run
 ,(ppur.purchased_part_id IS NOT NULL) 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 USING (part_id)
LEFT JOIN purchased_part ppur USING (part_id)
LEFT JOIN part_work_order_hold pwoh USING (part_id)
ORDER BY gp.part_num;

Other than that the VIEW definition looks fine.

Function

Can be largely simplified:

CREATE OR REPLACE FUNCTION pending_stock(var_part_id integer
 , var_pattern_id integer)
 RETURNS integer AS
$func$
DECLARE
 r record;
 var_qty_expected integer := 0;
 var_total_stock_moved_out integer := 0;
BEGIN
 FOR r IN
 SELECT work_order_id, quantity_expected
 FROM view_work_orders
 WHERE part_id = var_part_id
 AND open = 'TRUE' -- A string instead of a boolean?
 AND quantity_allocated IS NULL
 AND quantity_expected >= quantity_actual
 LOOP
 var_qty_expected := var_qty_expected + r.quantity_expected;
 SELECT var_total_stock_moved_out + COALESCE(sum(quantity), 0)
 FROM view_work_order_move_parts_details
 WHERE source_work_order_id = r.work_order_id
 AND part_id = var_part_id
 INTO var_total_stock_moved_out;
 END LOOP;
 RETURN GREATEST(var_qty_expected - var_total_stock_moved_out, 0);
END
$func$ LANGUAGE plpgsql

Major points

• Generally, assignments are comparatively expensive in plpgsql. Every assignment is executed with a (very simple and fast) SELECT statement internally. Try to use fewer of them.

• You can init variables at declaration time. No need for another statement.

• The assignment operator in plpgsql is :=. = works, but is undocumented.

• Use COALESCE() to catch NULL values.

• The function parameter var_pattern_id is never used. This is probably not the full function definition.

• The whole final part can be replaced with a single statement using GREATEST

Superior query

Now, this cleaned up function will be a bit faster, but not much. Your whole design of looping repeatedly is extremely inefficient. It results in correlated subqueries that loop through correlated subqueries yet again. Performance nightmare.

Recast the problem as set-based operation to make it faster. Well, a lot faster.

SELECT e.part_id
 ,GREATEST(COALESCE(sum(e.quantity_expected), 0)
 - COALESCE(sum(m.total_stock_moved_out), 0), 0)
FROM view_work_orders e
LEFT JOIN (
 SELECT source_work_order_id AS work_order_id
 ,COALESCE(sum(quantity), 0) AS total_stock_moved_out
 FROM view_work_order_move_parts_details
 WHERE part_id = var_part_id
 GROUP BY 1
 ) m USING (work_order_id)
WHERE e.part_id = var_part_id
AND e.open = 'TRUE'
AND e.quantity_allocated IS NULL
AND e.quantity_expected >= e.quantity_actual
GROUP BY 1;

Superior view

Integrate this into the original query / view:

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
 ,x.pending_amount
 ,p.production_run
 ,(ppur.purchased_part_id IS NOT NULL) 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 USING (part_id)
LEFT JOIN purchased_part ppur USING (part_id)
LEFT JOIN part_work_order_hold pwoh USING (part_id)
LEFT JOIN (
 SELECT e.part_id
 ,GREATEST(COALESCE(sum(e.quantity_expected), 0)
 - COALESCE(sum(m.total_stock_moved_out), 0)
 , 0) AS pending_amount
 FROM view_work_orders e
 LEFT JOIN (
 SELECT source_work_order_id AS work_order_id
 ,sum(quantity) AS total_stock_moved_out
 FROM view_work_order_move_parts_details
 WHERE part_id = var_part_id
 GROUP BY 1
 ) m USING (work_order_id)
 WHERE e.part_id = var_part_id
 AND e.open = 'TRUE'
 AND e.quantity_allocated IS NULL
 AND e.quantity_expected >= e.quantity_actual
 GROUP BY 1
 ) x USING (part_id)
ORDER BY gp.part_num;

Untested, obviously.

• Ram: Firstly Thanks a lot for the help. Even after making the changes suggested by you still i face the same problem. View is created successfully but when i just perform a query select count(*) from view_production_parts it takes ages to get the result. It should return around 26000 row count.
• Erwin Brandstetter: @Ram: Run EXPLAIN ANALYZE SELECT count(*) FROM view_production_parts, paste the output to explain.depesz.com and edit (left under your question) your question with the link. Also please supply the complete function definition in your question and your version of Postgres.
• Ram: Please find the edited post and hope it provides all the details you are looking for. I am new to Databases so my queries may seem bit naive. I apologize for the inconvenience.
• Ram: I am really pleased by your suggestions. The stored procedure i provided above isn't a complete one, i pasted the relevant part of the code that is of concern. I realized my mistakes and it helped me a lot. I now know why the view is slow. U provided me with a valuable insight. Thanks a lot