Запись, возвращаемая из функции, имеет объединенные столбцы

Question

У меня есть таблица, в которой хранятся изменения аккаунта с течением времени. Мне нужно объединить это с двумя другими таблицами, чтобы создать некоторые записи для определенного дня, если эти записи еще не существуют.

Чтобы упростить ситуацию (я надеюсь), я инкапсулировал запрос, который возвращает правильные исторические данные, в функцию, которая принимает идентификатор учетной записи и день.

Если я выполню "Select * account_servicetier_for_day(20424, '2014-08-12')"Я получаю ожидаемый результат (все данные возвращаются из функции в отдельных столбцах). Если я использую функцию в другом запросе, я получаю все столбцы, объединенные в один:

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

Я использую "PostgreSQL 9.2.4 на x86_64-slackware-linux-gnu, скомпилированный с помощью gcc (GCC) 4.7.1, 64-bit".

Запрос:

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

Функция:

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;

Answer 1

Как правило, чтобы разложить строки, возвращаемые функцией, и получить отдельные столбцы:

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.*   -- 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;

LATERAL здесь неявное ключевое слово, функции всегда могут ссылаться раньше FROM Предметы. Руководство:

LATERAL также может предшествовать вызову функции FROM item, но в данном случае это слово-шум, поскольку в любом случае выражение функции может ссылаться на более ранние элементы FROM.

Связанные с:

• Вставить несколько строк в одну таблицу на основе номера в другой таблице

Краткие обозначения с запятой в FROM список (в основном) эквивалентен CROSS JOIN LATERAL (такой же как [INNER] JOIN LATERAL ... ON TRUE) и, таким образом, удаляет строки из результата, когда вызов функции не возвращает строки. Чтобы сохранить такие строки, используйте LEFT JOIN LATERAL ... ON TRUE:

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

Кроме того, не используйте NOT IN (subquery) когда вы можете избежать этого. Это самый медленный и самый хитрый из нескольких способов сделать это:

• Выберите строки, которых нет в другой таблице

Я предлагаю NOT EXISTS вместо.

Postgres 9.2 или старше

Вы можете вызвать функцию возврата набора в SELECT список (который является расширением Postgres стандартного SQL). По соображениям производительности это лучше всего делать в подзапросе. Разложите (хорошо известный!) Тип строки во внешнем запросе, чтобы избежать повторной оценки функции:

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;

Связанный ответ Крейга Рингера с объяснением, почему мы лучше разлагаем во внешнем запросе:

• Как избежать множественных ошибок функций с помощью синтаксиса (func()).* В запросе SQL?

---

Postgres 10 наконец-то повторно реализовал функции, возвращающие множество в SELECT список, чтобы исправить неожиданные побочные эффекты.

Answer 2

Используйте функцию в from пункт

Select
    '2014-08-12' As day,
    0 As inbytes,
    0 As outbytes,
    acct.username,
    acct.accountid,
    acct.userid,
    asfd.*
From
    account_tab acct
    cross join lateral
    account_servicetier_for_day(acct.accountid, '2014-08-12') asfd
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