sql - PLpgSQL(或 ANSI SQL？)列上的条件计算

Question

我想编写一个对列执行条件计算的存储过程。理想情况下，SP 的实现将与数据库无关——如果可能的话。如果不是，则底层数据库是 PostgreSQL (v8.4)，因此它优先。

被查询的基础表如下所示:

CREATE TABLE treatment_def (  id         PRIMARY SERIAL KEY,
                    name       VARCHAR(16) NOT NULL
                 );

CREATE TABLE foo_group_def (  id         PRIMARY SERIAL KEY,
                    name       VARCHAR(16) NOT NULL
                 );

CREATE TABLE foo (  id         PRIMARY SERIAL KEY,
                    name       VARCHAR(16) NOT NULL,
                    trtmt_id   INT REFERENCES treatment_def(id) ON DELETE RESTRICT,
                    foo_grp_id INT REFERENCES foo_group_def(id) ON DELETE RESTRICT,
                    is_male    BOOLEAN NOT NULL,
                    cost       REAL NOT NULL
             );

我想编写一个 SP 返回以下“表格”结果集:

treatment_name, foo_group_name, averaged_cost

平均成本的计算方式不同，取决于行字段 *is_male* 标志设置为 true 还是 false。

为了这个问题的目的，假设如果 is_male 标志设置为 true，则平均成本计算为分组成本值的 SUM，如果 is_male标志设置为 false，则成本值计算为分组成本值的AVERAGE。

(显然)数据按 trmt_id、foo_grp_id(和 is_male？)分组。

如果没有对 is_male 标志的条件测试，我对如何编写 SQL 有一个大概的想法。但是，在编写上面定义的 SP 时，我需要一些帮助。

这是我的第一次尝试:

CREATE TYPE FOO_RESULT AS (treatment_name VARCHAR(16), foo_group_name VARCHAR(64), averaged_cost DOUBLE);      

// Outline plpgsql (Pseudo code)

CREATE FUNCTION somefunc() RETURNS SETOF FOO_RESULT AS $$
BEGIN
   RETURN QUERY SELECT t.name treatment_name, g.name group_name, averaged_cost  FROM foo f 
                     INNER JOIN treatment_def t ON t.id = f.trtmt_id
                     INNER JOIN foo_group_def g ON g.id = f.foo_grp_id
                GROUP BY f.trtmt_id, f.foo_grp_id;
END;
$$ LANGUAGE plpgsql;

对于如何正确编写此 SP 以在列结果中实现条件计算，我将不胜感激

Answer 1

可能看起来像这样:

CREATE FUNCTION somefunc()
 RETURNS TABLE (
  treatment_name varchar(16)
, foo_group_name varchar(16)
, averaged_cost double precision)
AS
$BODY$

    SELECT t.name          -- AS treatment_name
         , g.name          -- AS group_name
         , CASE WHEN f.is_male THEN sum(f.cost)
                               ELSE avg(f.cost) END -- AS averaged_cost  
    FROM   foo f 
    JOIN   treatment_def t ON t.id = f.trtmt_id
    JOIN   foo_group_def g ON g.id = f.foo_grp_id
    GROUP  BY 1, 2, f.is_male;

$BODY$ LANGUAGE sql;

要点

• 我使用了 sql 函数，而不是 plpgsql。你可以使用任何一个，我只是这样做来缩短代码。 plpgsql 可能会稍微快一些，因为查询计划已缓存。

• 我跳过了自定义复合类型。您可以使用 RETURNS TABLE 来更简单地做到这一点.

• 我通常建议使用 data type text而不是 varchar(n)。让您的生活更轻松。

• 注意不要在函数体中使用没有表限定 (tbl.col) 的 RETURN 参数名称，否则会造成命名冲突。这就是我评论别名的原因。

• 我调整了 GROUP BY 子句。原版没用。 (@Ken 的回答中也没有。)