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