Subject: Re: select into composite type / return
To: pgsql-sql@lists.postgresql.org
References: <39dcfab9-4eb2-382b-9186-baf722ca56a0@ringways.co.uk>
 <3523853.1616002010@sss.pgh.pa.us>
 <9eb42b06-9cfa-1e4b-e42b-6f64e99675a5@ringways.co.uk>
 <7af5d693-a6a3-d3d4-050a-0898aad8bd77@ringways.co.uk>
 <CAGqkgphmj3uHB3Jc5Eb=yA2cfffHFsACWR3Fbfr6jrkQepKJPA@mail.gmail.com>
 <3718235.1616077713@sss.pgh.pa.us>
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Message-ID: <e13395d5-10e5-3e83-1dd1-ad5d98d0e8b7@ringways.co.uk>
Date: Thu, 18 Mar 2021 14:32:50 +0000
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <3718235.1616077713@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/e13395d5-10e5-3e83-1dd1-ad5d98d0e8b7%40ringways.co.uk>
Precedence: bulk

On 18/03/2021 14:28, Tom Lane wrote:
> Beware --- what that actually does is expand into
> SELECT id, (do_breakdown(id)).f1, (do_breakdown(id)).f2, ...
>
> so that the function will be invoked N times if it produces N columns.
>
> What you generally want to do is invoke the function as a lateral FROM
> item:
>
> SELECT id, f.* FROM table AS t, LATERAL do_breakdown(t.id) AS f;
>
> 			regards, tom lane
Thanks for the info Tom, I can see how that would be quite a performance 
hit, not to mention adverse effects if these functions start doing updates.

gary=# SELECT id, f.* FROM sessions AS t, LATERAL do_breakdown(t.id) AS f;
  id |  f1   |  f2   |  f3   |  f4   |  f5   |  f6
----+-------+-------+-------+-------+-------+-------
   1 |  1.00 |  2.00 |  3.00 |  4.00 |  5.00 |  6.00
   2 | 11.00 | 12.00 | 13.00 | 14.00 | 15.00 | 16.00
   3 | 21.00 | 22.00 | 23.00 | 24.00 | 25.00 | 26.00
(3 rows)

gary=#