Why Are My PostgreSQL Function's Returned Columns Concatenated, and How Can I Fix It?

Record Returned from Function Has Columns Concatenated

When utilizing a function to retrieve data for a specific purpose, it may be encountered that the returned record's columns are concatenated into a single column. This can occur when the function is used as a subquery within another query.

Understanding Function Results

To decompose the row returned from the function and obtain the individual columns, simply execute the function separately:

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

This will provide output with the expected columnar structure.

Addressing the Original Query

To resolve the problem with the original query, there are multiple approaches depending on the version of PostgreSQL being used:

PostgreSQL 9.3 or Newer

Use JOIN LATERAL to connect the subquery and retain the row structure:

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;

PostgreSQL 9.2 or Older

Use a subquery to call the function and decompose the row in the outer query:

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;

In either approach, pay attention to potential duplicate column names when selecting the decomposed row.

The above is the detailed content of Why Are My PostgreSQL Function's Returned Columns Concatenated, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!