Hans-Jürgen Schönig <postgres@cybertec.at> writes:
> Consider the following scenario:
> select * from (Select nextval('seq_ab') as nv,
> * from ( select
> t_product.id,t_text.value,t_price.price
> from t_product,t_price,t_text
> where t_product.id = t_price.product_id
> and t_product.name = t_text.id
> and t_text.lang='de'
> and t_price.typ = 'default'
> order by price desc ) as t ) as u
> WHERE nv <= 1
> ;
I don't think there's any very clean way to fix this sort of problem in
general. We could make this particular example work if
(1) we prevented a subquery containing volatile functions in its
targetlist from being flattened into the parent query, and
(2) we prevented outer WHERE clauses from being pushed down into a
subquery when they reference subquery outputs containing volatile
functions.
There has been some recent discussion about doing (1) but I think we
forgot about the necessity to also do (2); otherwise you'd end up with
select * from (Select nextval('seq_ab') as nv, ... WHERE nextval('seq_ab') <=
1 ) as u ;
which is hardly any better.
Now those things are both doable but where it really falls down is when
you join the subselect to some other table. Short of materializing the
subselect there'd be no way to guarantee single evaluation of any one
row in the subselect.
I'd be willing to do (1) and (2) but not to force materialization; the
performance hit for that just seems unacceptable.
regards, tom lane