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