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@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of ext Andrew Sullivan
Sent: Friday, June 01, 2007 17:47
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Versionning (was: Whole-row comparison)

On Fri, Jun 01, 2007 at 08:07:46PM +0300, christian.roche.ext@nsn.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