From: | Oleksii Kliukin <alexk(at)hintbits(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: dubious optimization of the function in SELECT INTO target list |
Date: | 2015-10-06 20:48:19 |
Message-ID: | 0C250939-C943-4E58-9F17-513AA4B0AECE@hintbits.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On 06 Oct 2015, at 22:40, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 10/06/2015 01:13 PM, Oleksii Kliukin wrote:
>>
>> Basically, if we invoke the first example, the foo table with have only
>> 1 row and not 10, as supplied by the generate_series.
>> However, when ORDER BY is attached to the query, or aggregate (such as
>> max, min or array_agg) is wrapped around the test(id) call, the test
>> function is called exactly 10 times. If I replace the SELECT INTO with
>> PERFORM, it would also be called 10 times. Unfortunately, it is not
>> possible to use PERFORM directly in the CTE expression.
>
> What CTE expression?
Any CTE expression :-). The example here is just an illustration to expose the issue. The real-world query I came across used a complex CTE expression and called a function at the end of it inside the SELECT INTO statement.
>
> How about:
>
> DO $$
> DECLARE l_id integer;
> BEGIN
> FOR l_id IN SELECT id
> FROM generate_series(1,10) as id LOOP
> SELECT INTO l_id test(l_id);
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
This should work, but I'm interested in finding out why the original statement behaves the way I’ve described.
Kind regards,
--
Oleksii
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-10-06 20:50:51 | Re: dubious optimization of the function in SELECT INTO target list |
Previous Message | Adrian Klaver | 2015-10-06 20:40:17 | Re: dubious optimization of the function in SELECT INTO target list |