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,
