sql - 在 Postgresql 的 View 中使用存储过程

Question

我一直在尝试创建一个 View ，其中 pending_amount 列之一作为存储过程执行的结果获得其值。

存储过程是pending_stock(int,int) 并返回一个整数。 View 已成功创建，但是当我尝试在此 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
     , (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;

可以在 View 中使用存储过程吗？如果使用，我的声明是否正确？

找到 result from of this query at explain.depesz.com :

EXPLAIN ANALYZE SELECT count(*) FROM view_production_parts

我正在使用 Postgres 8.4。
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;

Answer 1

查看

函数调用不需要子查询。您还可以简化其他一些小细节:

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;

除此之外，VIEW 定义看起来还不错。

函数

可以大大简化:

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

要点

• 通常，plpgsql 中的赋值成本相对较高。每个赋值都在内部使用(非常简单和快速)SELECT 语句执行。尽量少用。

• 您可以在声明时初始化变量。无需再声明。

• The assignment operator in plpgsql is := . = 有效，但未记录。

• 使用 COALESCE()捕捉 NULL 值。

• 从未使用函数参数 var_pattern_id。这可能不是完整的函数定义。

• 可以使用 GREATEST 将整个最后部分替换为单个语句

高级查询

现在，这个清理函数会快一点，但不会快很多。您的整个重复循环设计效率极低。它导致相关子查询再次循环遍历相关子查询。性能噩梦。

将问题重铸为基于集合的操作以使其更快。嗯，很多更快。

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;

上 View

将其集成到原始查询/ 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;

显然未经测试。