PostgreSQL parametrized query

Question

I often do need to submit pretty much the same query but with different parameter (single parameter but multiple times within SELECT … clause (not WHERE(!))).

To illustrate, it's in every place where duc.opt_level >= 5 /*v_min_level*/ occurs within below:

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

…

Postgres ver. 9.5.1

Any elegant way of parameterizing this query so I can use call it simply only by redefining this parameter?

Something along the lines of anonymous PL/pgSQL block comes to mind but I don't know exactly how

Stored function or [PREPARE/EXECUTE statements](http://www.postgresql.org/docs/current/static/sql-prepare.html) for example. — Abelisto, May 19 '16 at 10:55
@joop how? do you mean to rewrite `CASE … WHEN` to `CASE WHEN …`? — msciwoj, May 19 '16 at 12:32
@Abelisto with PREPARE/EXECUTE I'm afraid it requires me to put lot of `?` and then call with multiple but same parameters: `execute pblock(5,5,5,5,5,5,…)` — msciwoj, May 19 '16 at 12:34
Yes: `CASE WHEN duc.du_type_cd = 'e' AND duc.opt_level < 5 AND duc.max_occurs <> 1 THEN duc.id_du ELSE r.id_du END entid` ... etc. (assuming NOT NULL) — joop, May 19 '16 at 15:30
No, just use `$1` inside query several times, fe: `select $1, $1 || $1 ...` — Abelisto, May 19 '16 at 16:14
Please post a *complete* query and always your version of Postgres. There's probably an even better solution. — Erwin Brandstetter, May 22 '16 at 23:43

score 0 · Answer 1 · answered May 19 '16 at 10:33

You can cross join to a "values" table:

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 >= p.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 >= p.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 >= p.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 >= p.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 >= p.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 >= p.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 >= p.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 ...
  cross join (values (5)) as p(min_level)

Does it not affect performance? (I know it's single-row dummy inline table but still…) — msciwoj, May 19 '16 at 12:36
I would be **very** surprised if that made any difference in the performance. But if you want to make sure, check the output of `explain analyze` — , May 19 '16 at 12:53

score 0 · Answer 2 · answered May 23 '16 at 00:41

Assuming all columns cannot be NULL (information missing in the question).

Your task becomes a lot simpler after untangling the CASE expressions to:

 , 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.

The same, repeated conditions can be concentrated in a single LEFT JOIN to another instance of r (r1) in the FROM list. Then default to alternative values with COALESCE if the conditions are not met:

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

Now you provide v_min_level only once. And the query is much shorter. May be a bit faster, too.

And never omit the keyword AS for column aliase. The manual:

Omitting the AS Key Word
...
In FROM items, both the standard and PostgreSQL allow AS to be omitted before an alias that is an unreserved keyword. But this is impractical for output column names, because of syntactic ambiguities.

Interesting approach. I suppose I'm looking at handling it in the context of PostgreSQL specific syntax/features (like. aforementioned `PREPARE/EXECUTE`) rather than rewriting the specific query. I don't see the point of sacrificing readability and performance (extra LEFT OUTER JOINS) for having single substitution placeholder. In this particular (hierarchical) query (updated) I think it's an overkill if at all possible. re. `AS` in aliases - agree, that's the good practice but to be consistent all SQL queries should always quote their table and column names to be 100% safe, don't they ;) ? — msciwoj, May 24 '16 at 09:58
@msciwoj: [About quoted identifiers.](http://stackoverflow.com/a/20880247/939860) As for performance: it remains to be seen which approach is faster. The join condition is evaluated *once* per row, while your `CASE` expressions are evaluated for every column- depends on how many you have. And if there's an index to support the expressions, the join may be substantially faster. Just test with `explain analyze`. Of course you can use prepared statements or functions with parameters. There are many related answers here on SO. — Erwin Brandstetter, May 24 '16 at 14:23

PostgreSQL parametrized query

2 Answers2