On 29.06.2016 15:30, David G. Johnston wrote:
In this subquery(below) we have reference to outer variables but it is not working as it should(or i dont understand something):
postgres=# postgres=# select id, ( select string_agg('a','') from generate_series(1,trunc(10*random()+1)::int) where id=id) from generate_series(1,10) as id;
id | string_agg
----+------------
1 | aaa
2 | aaa
...
but this query(with reference to outer var) working perfectly:
postgres=# select id,(select random() where id=id) from generate_series(1,10) as id;
id | random
----+--------------------
1 | 0.974509597290307
2 | 0.219822214450687
...
Also this query is working good( (id-id) do the job):
postgres=# select id, ( select string_agg('a','') from generate_series(1,trunc(10*random()+1)::int+(id-id)) ) from generate_series(1,10) as id;
id | string_agg
----+------------
1 | aaaaaaa
2 | aaaaa
...
It means that even reference to outer variables doesn't mean that executor execute volatile function from subquery every time. Or there is something else what i should know?
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company