Re: Versionning (was: Whole-row comparison)

Hi Andrew,

I must admit I don't really understand what you say. I don't know what
SRF stand for, and what you say about generic case is not clear to me,
sorry.

My idea is that using a parameter table allows me to keep using a view,
which is optimized for instance when used against a WHERE condition.

For example, I could write :

CREATE VIEW bsc_view AS
SELECT <fields>
FROM bsc_table
WHERE (obj_id, ver_id) IN
(SELECT obj_id, max(ver_id) FROM bsc_table, param_table
WHERE ver_id <= param_table.ver_id
GROUP BY obj_id));

and the following statement would be optimized:

UPDATE param_table SET ver_id = xxx;
SELECT * FROM bsc_view WHERE obj_id = yyy;

which would not be the case would I have used a multi-row function.

Does this make sense ?

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 17:47
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Versionning (was: Whole-row comparison)

On Fri, Jun 01, 2007 at 08:07:46PM +0300, christian(dot)roche(dot)ext(at)nsn(dot)com
wrote:
> I've contemplated reusing an awful hack from my Access era, namely
> using a single-rowed table to store the parameter and joining the view
on it.
> The parameter would be updated before the view is called; this would
> work but would definitely be ugly. Can someone think of a better way
> to do that ?

I sort of don't see how that hack would be any different from a SRF.
You'd lose the planner benefits anyway, I think, because you'd have to
plan for the generic case where the data could be anything, no?

A