From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Oleksii Kliukin <alexk(at)hintbits(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:50:51 |
Message-ID: | 5614342B.3080709@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 10/06/2015 01:48 PM, Oleksii Kliukin wrote:
>
>> On 06 Oct 2015, at 22:40, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto: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.
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Oleksii Kliukin | 2015-10-06 21:00:58 | Re: dubious optimization of the function in SELECT INTO target list |
Previous Message | Oleksii Kliukin | 2015-10-06 20:48:19 | Re: dubious optimization of the function in SELECT INTO target list |