Re: Versionning (was: Whole-row comparison)

Hi Andrew,

what is worrying me is that if I use a SRF, any additional WHERE
condition would not be taken into account before executing the
underlying query, e.g., in this request using a view, the WHERE
condition would be considered in the final query :

UPDATE params
SET version = ver_id;

SELECT *
FROM bsc_list_view
WHERE obj_id = 'xxx';

because the bsc_list_view would be expanded to the underlying request,
while using a SRF, the whole table would be scaned before the WHERE
condition is applied:

SELECT *
FROM bsc_list_srf(ver_id)
WHERE obj_id = 'xxx';

This is what I mean when I say that the optimization would be lost when
using a SRF. Now what is the "Right Thing To Do" in this particular
case ? The nicest thing would really to have parametrized view. Is
there any fundamental reason why such a beast does not exist, or is it
only postgres (compared to higher-level RDBMS) ?

Thanks a lot !
Christian

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of ext Andrew Sullivan
Sent: Friday, June 01, 2007 18:51
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Versionning (was: Whole-row comparison)

Yes, but I don't think it's true. Because you change the value of
ver_id all the time, the actual result can't be collapsed to a constant,
so you end up having to execute the query with the additional value, and
you still have to plan that. The same thing is true of a function,
which will have its plan prepared the first time you execute it. (I
could be wrong about this; I suppose the only way would be to try it.)