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

On 10/06/2015 01:48 PM, Oleksii Kliukin wrote:
>
>> On 06 Oct 2015, at 22:40, Adrian Klaver <adrian.klaver@aklaver.com
>> <mailto:adrian.klaver@aklaver.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.

Remember SELECT INTO inside plpgsql is different from SELECT INTO outside:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

"Tip: Note that this interpretation of SELECT with INTO is quite
different from PostgreSQL's regular SELECT INTO command, wherein the
INTO target is a newly created table. If you want to create a table from
a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE
... AS SELECT.

So a simple CTE example might help clear things up.

>
>
>>
>> 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
>


--
Adrian Klaver
adrian.klaver@aklaver.com