Record returned from function has columns concatenated

Question

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;

Higgins

When you select a record in scalar context, it gets converted to a string. You likely need to select from the function in a subquery and join that to account_tab. – Frei

@Frei It is not converted to a string. It is still the composite type account_dsl_history_info as returned by the function. – Indoaryan

@ClodoaldoNeto Fair point. I suspect when he goes to retrieve this value in his programming environment of choice, it's not going to understand how to represent it as anything but a string. (Some drivers can cope with record values, some can't.) – Frei

That's a pity, Postgres 9.3 would offer the convenient JOIN LATERAL. – Glori

Which answer should I accept? @ClodoaldoNeto answered correctly first based upon my question originally stating (incorrectly) I was using Postgresql 9.3. But ErwinBrandstetter gave a more complete answer posting info about v9.2. – Higgins

Answer 1

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 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 be wary of 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 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 it's better not to decompose on the same query level:

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

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

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

Glori

Answer 2

Use the function in the from clause

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

Indoaryan

I get syntax error at or near "." in account_servicetier_for_day(acct.accountid, ... – Higgins

It appears that lateral cross joins aren't supported on 9.2. Our dev (9.2.4) server is out of sync with our prod server (9.3.4). I will upgrade our dev server and get back to you. – Higgins