PostgreSQL参数化查询

Question

我经常需要提交几乎相同的查询，但使用不同的参数(单个参数，但在SELECT …子句中多次提交(不是WHERE(！))。

为了说明这一点，它位于duc.opt_level >= 5 /*v_min_level*/出现在下面的每一个地方：

SELECT duc.id_du, duc.du_type_cd, duc.du_name, duc.du_addr, duc.id_du_def, duc.def_repeat_flg, defc.allows_txt_flg
  ,r.lvl + CASE duc.du_type_cd WHEN 'e' THEN CASE WHEN duc.opt_level >= 5 /*v_min_level*/ AND duc.max_occurs=1 THEN 0 ELSE 1 END ELSE 0 END lvl
  ,CASE duc.du_type_cd WHEN 'e' THEN CASE WHEN duc.opt_level >= 5 /*v_min_level*/ AND duc.max_occurs=1 THEN r.id_du ELSE duc.id_du END ELSE r.id_du END entid
  ,CASE duc.du_type_cd WHEN 'e' THEN CASE WHEN duc.opt_level >= 5 /*v_min_level*/ AND duc.max_occurs=1 THEN r.entraw ELSE duc.du_addr END ELSE r.entraw END entraw
  ,CASE duc.du_type_cd WHEN 'e' THEN CASE WHEN duc.opt_level >= 5 /*v_min_level*/ AND duc.max_occurs=1 THEN r.entnm ELSE duc.du_name END ELSE r.entnm END entnm
  ,CASE duc.du_type_cd WHEN 'e' THEN CASE WHEN duc.opt_level >= 5 /*v_min_level*/ AND duc.max_occurs=1 THEN r.pentid ELSE r.entid END ELSE r.pentid END pentid
  ,CASE duc.du_type_cd WHEN 'e' THEN CASE WHEN duc.opt_level >= 5 /*v_min_level*/ AND duc.max_occurs=1 THEN r.pentraw ELSE r.entraw END ELSE r.pentraw END pentraw
  ,CASE duc.du_type_cd WHEN 'e' THEN CASE WHEN duc.opt_level >= 5 /*v_min_level*/ AND duc.max_occurs=1 THEN r.pentnm ELSE r.entnm END ELSE r.pentnm END pentnm
  ,defc.def_type, defc.val_type
  ,defc.is_nillable
  ,defc.optional
FROM r
JOIN data_unit duc
  ON duc.id_parent_du = r.id_du
JOIN du_def defc
  ON defc.id_du_def = duc.id_du_def

…

波斯特格雷斯。9.5.1

有什么优雅的方法来参数化这个查询，所以我只需要通过重新定义这个参数就可以使用调用它？

我想到了一些类似于匿名PL/pgSQL块的东西，但我不知道具体是如何实现的。

Answer 1

假设所有列都不能为空(问题中缺少信息)。

在解开CASE表达式后，您的任务变得简单得多：

 , CASE WHEN duc.du_type_cd = 'e' AND (duc.opt_level < 5 OR duc.max_occurs <> 1) THEN r.lvl + 1   ELSE r.lvl     END AS lvl
 , CASE WHEN duc.du_type_cd = 'e' AND (duc.opt_level < 5 OR duc.max_occurs <> 1) THEN duc.id_du   ELSE r.id_du   END AS entid
 , CASE WHEN duc.du_type_cd = 'e' AND (duc.opt_level < 5 OR duc.max_occurs <> 1) THEN duc.du_addr ELSE r.entraw  END AS entraw
  -- etc.

同样，重复的条件可以集中在单个LEFT JOIN中，到r (r1)在FROM列表中的另一个实例中。如果不满足条件，则默认为COALESCE的替代值：

SELECT duc.id_du, duc.du_type_cd, duc.du_name, duc.du_addr, duc.id_du_def, duc.def_repeat_flg
     , defc.allows_txt_flg, defc.def_type, defc.val_type, defc.is_nillable, defc.optional
     , COALESCE(r1.lvl    , r.lvl + 1)   AS lvl
     , COALESCE(r1.id_du  , duc.id_du)   AS entid
     , COALESCE(r1.entraw , duc.du_addr) AS entraw
     , COALESCE(r1.entnm  , duc.du_name) AS entnm
     , COALESCE(r1.pentid , r.entid)     AS pentid
     , COALESCE(r1.pentraw, r.entraw)    AS pentraw
     , COALESCE(r1.pentnm , r.entnm)     AS pentnm
FROM   duc
JOIN   defc ON ???  -- missing information
JOIN   r    ON ???  -- missing information
LEFT   JOIN r1 ON duc.du_type_cd <> 'e'
               OR (duc.opt_level >= 5 /*v_min_level*/ AND duc.max_occurs = 1);

现在您只提供一次v_min_level。查询要短得多。可能也会更快一些。

千万不要忽略列别名的关键字AS。手册：

AS 省略关键字 ..。 在FROM项中，标准和PostgreSQL都允许在别名(即非保留关键字)之前省略AS。但是对于输出列名来说，这是不切实际的，因为语法上有歧义。