Registro retornado da function possui colunas concatenadas

Eu tenho uma tabela que armazena as alterações de conta ao longo do tempo. Eu preciso juntar isso com duas outras tabelas para criar alguns registros para um dia específico, se esses registros já não existirem.

Para tornar as coisas mais fáceis (espero), encapsulei a consulta que retorna os dados históricos corretos em uma function que aceita um id de conta e o dia.

Se eu executar "Select * account_servicetier_for_day(20424, '2014-08-12')" , recebo o resultado esperado (todos os dados retornados da function em colunas separadas). Se eu usar a function em outra consulta, coloco todas as colunas em uma só:

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

Estou usando o “PostgreSQL 9.2.4 no x86_64-slackware-linux-gnu, compilado pelo gcc (GCC) 4.7.1, 64-bit”.

Inquerir:

 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 

Função:

 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; 

Geralmente, para decompor linhas retornadas de uma function e obter colunas individuais:

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


Quanto à consulta:

Postgres 9.3+

Limpador com o 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; 

A palavra-chave LATERAL está implícita aqui, as funções podem sempre se referir a itens anteriores de FROM . O manual:

LATERAL também pode preceder um item FROM chamada de function, mas neste caso é uma palavra de ruído, porque a expressão de function pode se referir a itens anteriores de FROM em qualquer caso.

Relacionado:

  • Inserir várias linhas em uma tabela com base no número em outra tabela

As notações curtas com uma vírgula na lista FROM são (principalmente) equivalentes a um CROSS JOIN LATERAL (o mesmo que [INNER] JOIN LATERAL ... ON TRUE ) e, portanto, remove linhas do resultado onde a chamada de function não retorna nenhuma linha. Para manter essas linhas, 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 ... 

Além disso, não use NOT IN (subquery) quando puder evitá-lo. É o mais lento e mais complicado de várias maneiras de fazer isso:

  • Selecione linhas que não estão presentes em outra tabela

Eu sugiro que NOT EXISTS lugar.

Postgres 9.2 ou mais

Você pode chamar uma function set-returning na lista SELECT (que é uma extensão Postgres do SQL padrão). Por motivos de desempenho, isso é feito melhor em uma subconsulta. Decomponha o tipo de linha (bem conhecido!) Na consulta externa para evitar a avaliação repetida da 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; 

Resposta relacionada por Craig Ringer com uma explicação, porque é melhor nos decompormos na consulta externa:

  • Como evitar várias funções evals com a syntax (func ()). * Em uma consulta SQL?

O Postgres 10 finalmente reimplementou as funções set-returning na lista SELECT para corrigir efeitos colaterais inesperados.

Use a function na cláusula 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