Record Returned from Function Has Columns Concatenated

Record returned from function has columns concatenated

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?

why postgres functions returns result in one column?

If you want the result as a set of columns, then you need:

SELECT * FROM public.get_direction();

Return table with columns

select * from foo4(4); should give you the result you are looking for.

How to return different format of records from a single PL/pgSQL function?

You can make it work by returning a superset as row: comprised of item and collection. One of both will be NULL for each result row.

WITH RECURSIVE ancestors AS (
   SELECT 0 AS lvl, i.parent_id, i.parent_table, i AS _item, NULL::collections AS _coll
   FROM   items i
   WHERE  item_id IN ( ${itemIds} )

   UNION ALL -- !
   SELECT lvl + 1, COALESCE(i.parent_id, c.parent_id), COALESCE(i.parent_table, 'i'), i, c
   FROM   ancestors        a 
   LEFT   JOIN items       i ON a.parent_table = 'i' AND i.item_id = a.parent_id
   LEFT   JOIN collections c ON a.parent_table = 'c' AND c.collection_id = a.parent_id
   WHERE  a.parent_id IS NOT NULL
   )
SELECT lvl, _item, _coll
FROM   ancestors
--     ORDER BY ?

db<>fiddle here

UNION ALL, not UNION.

Assuming a collection's parent is always an item, while an item can go either way.

We need LEFT JOIN on both potential parent tables to stay in the race.

I added an optional lvl to keep track of the level of hierarchy.

About decomposing row types:

• Combine postgres function with query
• Record returned from function has columns concatenated

Concatenating All Columns of Each Record Into One Entry

After a lot more work on this, I came up with an answer. Many thanks to help provided in this different, but related thread: How to Refer to a Column by ID or Index Number

Bottom line: I created a query with dynamic SQL then ran it with EXECUTE IMMEDIATE. The results were looped through and output one by one. It was a much more elegant solution.

DECLARE
    j    number := 0;
    sql_query    varchar2(32000);
    l_tableheaders    varchar2(32000);
    TYPE array_type IS TABLE OF varchar2(200) NOT NULL index by binary_integer;
    Data_Array array_type;
    MyTableName := 'TableName';

BEGIN
    SELECT LISTAGG(column_name, ' || '','' || ') WITHIN GROUP (ORDER BY column_id)
        INTO l_tableheaders FROM all_tab_cols WHERE table_name = MyTableName;

    sql_query := ' SELECT ' || l_tableheaders || ' FROM ' || MyTableName;

    EXECUTE IMMEDIATE sql_query BULK COLLECT INTO Data_Array;

    FOR j in 1..Data_Array.Count
    LOOP
        DBMS_OUTPUT.PUT ( Data_Array(j) );
        DBMS_OUTPUT.NEW_LINE;
    END LOOP;

END;

Concatenate rows in function PostgreSQL

You do not need PL/pgSQL for that.

First eliminate duplicate names using DISTINCT and then in a subquery you can concat the columns into a single string. After that use array_agg to create an array out of it. It will then "merge" multiple arrays, in case the subquery returns more than one row. Finally, get rid of the commas and curly braces using array_to_string. Instead of using the char value of a newline, you can simply use E'\n' (E stands for escape):

WITH j (name,location,team_id,start,end_) AS (
  VALUES ('Library','Atlanta',2389,2015,2017),
         ('Library','Georgetown',9920,2003,2007),
         ('Museum','Auckland',3092,2005,2007)
)
SELECT 
  DISTINCT q1.name,
  array_to_string(
  (SELECT array_agg(concat(location,', ',team_id,', ',start,'-', end_, E'\n'))
   FROM j WHERE name = q1.name),'') AS records
FROM j q1;

  name   |           records           
---------+----------------------------
 Library | Atlanta, 2389, 2015-2017   
         | Georgetown, 9920, 2003-2007
         | 
 Museum  | Auckland, 3092, 2005-2007  

• Note: try to not use reserved strings (e.g. end,name,start, etc.) to name your columns. Although PostgreSQL allows you to use them, it is considered a bad practice.

Demo: db<>fiddle

In PostgreSQL how do I create a function that returns an actual table?

The function is fine, it's how you're calling it.

Instead of:

SELECT cols('test');

use

SELECT * FROM cols('test');

---

Related Topics