sql - 将列添加到 %rowtype 的记录变量

Question

我在 PostgreSQL 9.2.9 中有一个 plpgsql 函数需要返回多个列。大多数列都来自一个表，所以我声明了虚拟表(这个术语正确吗？)rc 为:

rc "Sequence"%rowtype;

这非常有效，除了我有额外的 2 列要添加到返回的数据中。如果我将上面的声明替换为:

 rc RECORD;

以及其他一些小的代码更改。但随后我需要始终使用包含所有列定义的非常长的 SELECT 命令明确调用该函数。

我如何才能两全其美，即返回 "Sequence" 表的所有列并添加 2 个新列("AverageSED" 和 "avDailySED") 与一个简单的:

SELECT * FROM production1('2016-02-27 00:00:00','2016-03-11 00:00:00');

这是我使用 RECORD 类型的通用函数示例(为了便于阅读而删减)。

CREATE OR REPLACE FUNCTION production1(tme1 timestamp without time zone, tme2 timestamp without time zone, mn integer)
  RETURNS SETOF record AS
$BODY$
DECLARE
 -- Could possibly use (CREATE TYPE rcholder %rowtype) and use a function with a sub-function that then takes the rc RECORD and recasts it to type %rowtype. This would mean the column descriptor in the SELECT call could be dropped
  rc record;
  AverageSED Real;
  avDailySED Real;

BEGIN
-- Calculate average of value from Log_Alpha table
SELECT AVG("logSED")::Real
    FROM "Log_Alpha"
    WHERE "logTime" >= tme1 AND "logTime" < tme2
    INTO averageSED;

-- Select the required row from the sequence data table
  FOR rc IN
    SELECT *, AverageSED, avDailySED
    FROM "Sequence"
    WHERE "Sequence"."seqMinute" = mn AND "Sequence"."seqTime" >= tmeA AND "Sequence"."seqTime" <= tmeB
    ORDER BY "Sequence"."seqTime"
  LOOP
    rc."seqTime" = rc."seqTime" - '1 day'::interval;

  -- Use a subquery to calculate the average SED for all data for the day pertaining to this record date field
    SELECT AVG("logSED")::Real into rc.avDailySED FROM "Log_Alpha"
    WHERE "Log_Alpha"."logTime" >= rc."seqTime" and "Log_Alpha"."logTime" < (rc."seqTime" + '1 day'::interval);

    RETURN NEXT rc ;-- (AverageSED,avDailySED);
  END LOOP;
END
$BODY$
  LANGUAGE plpgsql;

因此，由于 “Sequence” 表中的所有可用列，此函数需要一个超过 1400 个字符的非常长的 SELECT 查询。

Answer 1

你的问题是函数的返回类型，不是变量rc在函数内部，它工作得很好。

您可以创建另一种类型以在 RETURNS SETOF <row_type> 中使用, like @Pavel demonstrates . (但不要在这里使用继承，这是学术方面的问题，就像 Pavel 已经提到的那样。)

或者您可以使用 RETURNS TABLE () 反而。 (这就是我会做的。)

此外，您的整个功能可以从根本上简化为一个带有 LATERAL 的 SQL 查询。子查询(需要 Postgres 9.3+，考虑升级到当前版本!):

CREATE OR REPLACE FUNCTION production1(tme1 timestamp, tme2 timestamp, mn int)
  RETURNS TABLE ( -- list all columns of "Sequence" here! Plus ...
                , average_sed  real
                , av_daily_sed real) AS
$func$
   SELECT s.*, a1.average_sed, a2.av_daily_sed
   FROM   "Sequence" s
   CROSS JOIN (  -- safe for an aggregate that always returns a row
      SELECT AVG("logSED")::real AS average_sed
      FROM   "Log_Alpha"
      WHERE  "logTime" >= tme1
      AND    "logTime" <  tme2
      ) a1
   LEFT JOIN LATERAL (
      SELECT AVG("logSED")::real AS av_daily_sed
      FROM   "Log_Alpha" a
      WHERE  a."logTime" >= (s."seqTime" - interval '1 day')
      and    a."logTime" <   s."seqTime" 
      ) a2 ON TRUE
   WHERE  s."seqMinute" = mn
   AND    s."seqTime" >= tme1
   AND    s."seqTime" <  tme2  -- I assume you want '<' like above
   ORDER  BY s."seqTime";
$func$  LANGUAGE sql STABLE;

唯一的区别:"seqTime"返回不变，而不是 "seqTime" - '1 day'::interval就像在你的函数中一样(不确定这是否是有意的)。

相关:

• What is the difference between LATERAL and a subquery in PostgreSQL?

旁白

没有办法向 plpgsql 中的任何行类型变量“添加”列。无论是众所周知的行类型 ( %ROWTYPE ) 还是匿名的 record - 可以重新分配，但分配后不会添加列。

---

so I have declared the virtual table (is that term correct?) rc as:

rc "Sequence"%rowtype;

The manual clarifies:

A variable of a composite type is called a row variable (or row-type variable).

在阅读该章(推荐!)时还要注意:

A row variable can be declared to have the same type as the rows of an existing table or view, by using the table_name%ROWTYPE notation; or it can be declared by giving a composite type's name. (Since every table has an associated composite type of the same name, it actually does not matter in PostgreSQL whether you write %ROWTYPE or not. But the form with %ROWTYPE is more portable.)

---

我强烈建议只使用合法的、小写的、不带引号的标识符。您的 CaMeL 大小写标识符是一把上了膛的步兵枪，尤其是当您有时使用双引号有时不使用双引号时。

• Are PostgreSQL column names case-sensitive?