From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@lists.postgresql.org
Subject: Re: select into composite type / return
Date: Thu, 18 Mar 2021 14:32:50 +0000
Message-ID: <e13395d5-10e5-3e83-1dd1-ad5d98d0e8b7@ringways.co.uk> (raw)
In-Reply-To: <3718235.1616077713@sss.pgh.pa.us>
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>
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=#
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk, pgsql-sql@lists.postgresql.org
Subject: Re: select into composite type / return
In-Reply-To: <e13395d5-10e5-3e83-1dd1-ad5d98d0e8b7@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox