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:
"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
My question was, essentially, if SELECT INTO in pl/pgSQL is supposed to stop after emitting the first row, ignoring the fact that the expression it calls may have side effects. I think I’ve got the answer from Tom that yes, it is supposed to be so, but I still think the docs are quite ambiguous about it (i.e. I read "Any result rows after the first row are discarded.” in the SELECT INTO description as a possible sign that they are still evaluated).
