从函数返回的记录具有连接的列 [英] Record returned from function has columns concatenated

问题描述

我有一张表，用于存储帐户随时间的变化.如果这些记录不存在，我需要将它与另外两个表连接起来以创建特定日期的一些记录.

为了让事情更简单(我希望如此)，我将返回正确历史数据的查询封装到一个函数中，该函数接收帐户 ID 和日期.

如果我执行 "Select * account_servicetier_for_day(20424, '2014-08-12')"，我会得到预期的结果(函数返回的所有数据都在单独的列中).如果我在另一个查询中使用该函数，则会将所有列合并为一个:

("2014-08-12 14:20:37",hollenbeck,691,12129,20424,69.95,"2Mb/1Mb 20GB 限制",2048,1024,20.000)

我在 x86_64-slackware-linux-gnu 上使用PostgreSQL 9.2.4，由 gcc (GCC) 4.7.1，64 位编译".

查询:

选择'2014-08-12' 作为天，0 作为 inbytes，0 作为 outbytes，acct.username，acct.accountid，acct.userid，account_servicetier_for_day(acct.accountid, '2014-08-12')从 account_tab 帐户其中 acct.isdsl = 1并且 acct.dslservicetypeid 不为空而acct.accountid不在(从dailyaccounting_tab中选择accountid Where Day = '2014-08-12')按 acct.username 排序

功能:

CREATE OR REPLACE FUNCTION account_servicetier_for_day(_accountid integer, _day timestamp without time zone) RETURNS setof account_dsl_history_info AS$BODY$DECLARE _accountingrow 记录；开始退货查询选择 * 从 account_dsl_history_info其中 accountid = _accountid And timestamp <= _day + interval '1 天 - 1 毫秒'按时间戳排序 Desc限制 1；结尾;$BODY$ 语言 plpgsql;

解决方案

一般来说，分解从函数返回的行并获取单个列:

SELECT * FROM account_servicetier_for_day(20424, '2014-08-12');

---

关于查询:

Postgres 9.3 或更新版本

使用 JOIN LATERAL 清理:

SELECT '2014-08-12' AS day, 0 AS inbytes, 0 AS outbytes, a.username, a.accountid, a.userid, f.* -- 但要避免重复的列名！FROM account_tab a, account_servicetier_for_day(a.accountid, '2014-08-12') f -- <-- 这里哪里 a.isdsl = 1并且 a.dslservicetypeid 不为空并且不存在 (选择 1FROM dailyaccounting_tabWHERE 天 = '2014-08-12'AND accountid = a.accountid)ORDER BY a.username;

LATERAL 关键字在这里是隐含的，函数总是可以引用较早的FROM 项.手册:

<块引用>

LATERAL 也可以在函数调用 FROM 项之前，但在这个如果是干扰词，因为函数表达式可以参考在任何情况下都较早的 FROM 项.

相关:

• 插入一个表中的多行基于另一表中的数字

FROM 列表中带逗号的短符号(大部分)等效于 CROSS JOIN LATERAL(与 [INNER] JOIN LATERAL ...ON TRUE) 并因此从函数调用不返回任何行的结果中删除行.要保留这些行，请使用 LEFT JOIN LATERAL ... ON TRUE:

...FROM account_tab aLEFT JOIN LATERAL account_servicetier_for_day(a.accountid, '2014-08-12') f ON TRUE...

另外，在可以避免的情况下不要使用 NOT IN (subquery).这是执行此操作的几种方法中最慢和最棘手的:

• 选择不存在于其他表

我建议 NOT EXISTS 代替.

Postgres 9.2 或更早版本

您可以在 SELECT 列表(它是标准 SQL 的 Postgres 扩展)中调用集合返回函数.出于性能原因，这最好在子查询中完成.分解外部查询中的(众所周知的！)行类型以避免重复评估函数:

SELECT '2014-08-12' AS day, 0 AS inbytes, 0 AS outbytes, a.username, a.accountid, a.userid, (a.rec).* -- 但要避免重复的列名！从  (SELECT *, account_servicetier_for_day(a.accountid, '2014-08-12') AS recFROM account_tab a哪里 a.isdsl = 1并且 a.dslservicetypeid 不为空并且不存在 (选择 1FROM dailyaccounting_tabWHERE 天 = '2014-08-12'AND accountid = a.accountid)) 一种ORDER BY a.username;

Craig Ringer 的相关回答并解释了为什么我们更好地在外部查询中分解:

• 如何在 SQL 查询中使用 (func()).* 语法避免多个函数评估?

Postgres 10 删除了 SELECT 中集合返回函数行为的奇怪之处:<​​/p>

• SELECT 子句中多个返回集合的函数的预期行为是什么?

I have a table which stores account changes over time. I need to join that up with two other tables to create some records for a particular day, if those records don't already exist.

To make things easier (I hope), I've encapsulated the query that returns the correct historical data into a function that takes in an account id, and the day.

If I execute "Select * account_servicetier_for_day(20424, '2014-08-12')", I get the expected result (all the data returned from the function in separate columns). If I use the function within another query, I get all the columns joined into one:

("2014-08-12 14:20:37",hollenbeck,691,12129,20424,69.95,"2Mb/1Mb 20GB Limit",2048,1024,20.000)

I'm using "PostgreSQL 9.2.4 on x86_64-slackware-linux-gnu, compiled by gcc (GCC) 4.7.1, 64-bit".

Query:

Select
    '2014-08-12' As day, 0 As inbytes, 0 As outbytes, acct.username, acct.accountid, acct.userid,
    account_servicetier_for_day(acct.accountid, '2014-08-12')
From account_tab acct
Where acct.isdsl = 1
    And acct.dslservicetypeid Is Not Null
    And acct.accountid Not In (Select accountid From dailyaccounting_tab Where Day = '2014-08-12')
Order By acct.username

Function:

CREATE OR REPLACE FUNCTION account_servicetier_for_day(_accountid integer, _day timestamp without time zone) RETURNS setof account_dsl_history_info AS
$BODY$
DECLARE _accountingrow record;
BEGIN
  Return Query
  Select * From account_dsl_history_info
  Where accountid = _accountid And timestamp <= _day + interval '1 day - 1 millisecond'
  Order By timestamp Desc 
  Limit 1;
END;
$BODY$ LANGUAGE plpgsql;

解决方案

Generally, to decompose rows returned from a function and get individual columns:

SELECT * FROM account_servicetier_for_day(20424, '2014-08-12');

---

As for the query:

Postgres 9.3 or newer

Cleaner with JOIN LATERAL:

SELECT '2014-08-12' AS day, 0 AS inbytes, 0 AS outbytes
     , a.username, a.accountid, a.userid
     , f.*   -- but avoid duplicate column names!
FROM   account_tab a
     , account_servicetier_for_day(a.accountid, '2014-08-12') f  -- <-- HERE
WHERE  a.isdsl = 1
AND    a.dslservicetypeid IS NOT NULL
AND    NOT EXISTS (
   SELECT 1
   FROM   dailyaccounting_tab
   WHERE  day = '2014-08-12'
   AND    accountid = a.accountid
   )
ORDER  BY a.username;

The LATERAL keyword is implicit here, functions can always refer earlier FROM items. The manual:

LATERAL can also precede a function-call FROM item, but in this case it is a noise word, because the function expression can refer to earlier FROM items in any case.

Related:

• Insert multiple rows in one table based on number in another table

Short notation with a comma in the FROM list is (mostly) equivalent to a CROSS JOIN LATERAL (same as [INNER] JOIN LATERAL ... ON TRUE) and thus removes rows from the result where the function call returns no row. To retain such rows, use LEFT JOIN LATERAL ... ON TRUE:

...
FROM  account_tab a
LEFT  JOIN LATERAL account_servicetier_for_day(a.accountid, '2014-08-12') f ON TRUE
...

Also, don't use NOT IN (subquery) when you can avoid it. It's the slowest and most tricky of several ways to do that:

• Select rows which are not present in other table

I suggest NOT EXISTS instead.

Postgres 9.2 or older

You can call a set-returning function in the SELECT list (which is a Postgres extension of standard SQL). For performance reasons, this is best done in a subquery. Decompose the (well-known!) row type in the outer query to avoid repeated evaluation of the function:

SELECT '2014-08-12' AS day, 0 AS inbytes, 0 AS outbytes
     , a.username, a.accountid, a.userid
     , (a.rec).*   -- but avoid duplicate column names!
FROM  (
   SELECT *, account_servicetier_for_day(a.accountid, '2014-08-12') AS rec
   FROM   account_tab a
   WHERE  a.isdsl = 1
   AND    a.dslservicetypeid Is Not Null
   AND    NOT EXISTS (
       SELECT 1
       FROM   dailyaccounting_tab
       WHERE  day = '2014-08-12'
       AND    accountid = a.accountid
      )
   ) a
ORDER  BY a.username;

Related answer by Craig Ringer with an explanation, why we better decompose in the outer query:

• How to avoid multiple function evals with the (func()).* syntax in an SQL query?

Postgres 10 removed oddities in the behavior of set-returning functions in the SELECT:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

这篇关于从函数返回的记录具有连接的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！