On 10/06/2015 02:00 PM, Oleksii Kliukin wrote:
On 06 Oct 2015, at 22:50, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
On 10/06/2015 01:48 PM, Oleksii Kliukin wrote:
On 06 Oct 2015, at 22:40, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx>> 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.
Thank you. In this case SELECT INTO was consciously called inside the
pl/pgSQL function, as one cannot do SELECT function() from pl/pgSQL
without storing the result of the function somewhere (with the INTO clause).
So what you asking is why to replicate this:
DECLARE l_id integer;
BEGIN
PERFORM test(id)
FROM generate_series(1,10) as id ;
END;
$$ LANGUAGE plpgsql;
you have to do something like this?:
DO $$
DECLARE l_id integer;
BEGIN
SELECT test(id) INTO l_id
FROM generate_series(1,10) AS id order by id;
END;
$$ LANGUAGE plpgsql;
DO
The problem itself has nothing to do with CTEs, the only reason why I’ve
mentioned it is to justify why I didn’t use PERFORM instead of SELECT
INTO (the following thread
http://www.postgresql.org/message-id/91873FFA-838D-4A16-ABED-A0255ED5168F@xxxxxxxxxxxxxxx gives
more details, although it is irrelevant to the problem being described).
Kind regards
--
Oleksii
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
