Re: dubious optimization of the function in SELECT INTO target list

> On 08 Oct 2015, at 16:00, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 10/08/2015 01:57 AM, Oleksii Kliukin wrote:
>>
>>> On 06 Oct 2015, at 23:31, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>
>>> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>> wrote:
>>>
>>> Oleksii Kliukin <alexk(at)hintbits(dot)com <mailto:alexk(at)hintbits(dot)com> <mailto:alexk(at)hintbits(dot)com <mailto:alexk(at)hintbits(dot)com>>> writes:
>>>> This should work, but I'm interested in finding out why the original
>>>> statement behaves the way I’ve described.
>>>
>>> plpgsql's SELECT INTO is only capable of storing a single result row,
>>> so it only executes the statement far enough to obtain one row, and
>>> then stops (as though a LIMIT were present). There is no guarantee
>>> about how much useless computation will get done underneath.
>>
>> Thank you, now it’s clear. I have to say there is no guarantee that the
>> computation would be useless. Someone might be calling a function that
>> updates/deletes rows in the SELECT INTO block, being forced to use
>> SELECT INTO by inability of pl/pgSQL to just discard the result of a
>> normal SELECT. I know one can use a loop or call PERFORM, but in some
>> cases (a complex CTE computing the data for the function being called at
>> the end, which updates the tables with this data) actually using SELECT
>> INTO looks like the easiest path to achieve the desired result.
>
> Well the best I can come up with at the moment is:
>
> DO $$
> DECLARE l_id integer;
> BEGIN
> WITH gs AS (select generate_series(1,10) as id)
> SELECT test(id) FROM gs ORDER BY id INTO l_id;
> END;
> $$ LANGUAGE plpgsql;

Yeah, or use max/min/some other aggregate instead of ORDER BY.

Kind regards,
--
Oleksii