Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 9F8429FB3DD
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  1 Jun 2007 15:07:19 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 69057-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  1 Jun 2007 15:07:13 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mgw-ext11.nokia.com (smtp.nokia.com [131.228.20.170])
	by postgresql.org (Postfix) with ESMTP id 1D3099FA134
	for <pgsql-sql@postgresql.org>; Fri,  1 Jun 2007 15:07:14 -0300 (ADT)
Received: from esebh107.NOE.Nokia.com (esebh107.ntc.nokia.com
 [172.21.143.143])
	by mgw-ext11.nokia.com (Switch-3.2.5/Switch-3.2.5) with ESMTP id
	l51I79CC014538; Fri, 1 Jun 2007 21:07:10 +0300
Received: from esebh104.NOE.Nokia.com ([172.21.143.34]) by
	esebh107.NOE.Nokia.com with Microsoft SMTPSVC(6.0.3790.1830);
	Fri, 1 Jun 2007 21:07:08 +0300
Received: from esebe111.NOE.Nokia.com ([172.21.143.154]) by
	esebh104.NOE.Nokia.com with Microsoft SMTPSVC(6.0.3790.1830);
	Fri, 1 Jun 2007 21:07:08 +0300
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Versionning (was: Whole-row comparison)
Date: Fri, 1 Jun 2007 21:07:10 +0300
Message-ID: <CCC9DCA122011F4CA593F6A548BFFBD362FCFE@esebe111.NOE.Nokia.com>
In-Reply-To: <20070601174711.GH24299@phlogiston.dyndns.org>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] Versionning (was: Whole-row comparison)
Thread-Index: AcekdU68/ARfvSUpTqezpJ7rC2TrBwAAUGyg
References: <CCC9DCA122011F4CA593F6A548BFFBD354D259@esebe111.NOE.Nokia.com>
	<473557.7523.qm@web31803.mail.mud.yahoo.com>
	<CCC9DCA122011F4CA593F6A548BFFBD354D260@esebe111.NOE.Nokia.com>
	<CCC9DCA122011F4CA593F6A548BFFBD362FCF9@esebe111.NOE.Nokia.com>
	<20070601174711.GH24299@phlogiston.dyndns.org>
From: <christian.roche.ext@nsn.com>
To: <ajs@crankycanuck.ca>
Cc: <pgsql-sql@postgresql.org>
X-OriginalArrivalTime: 01 Jun 2007 18:07:08.0990 (UTC)
	FILETIME=[AB923DE0:01C7A477]
X-Nokia-AV: Clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.839 tagged_above=0 required=5 tests=AWL=-0.123,
	BAYES_50=0.001, NO_REAL_NAME=0.961
X-Spam-Level: 
X-Archive-Number: 200706/3
X-Sequence-Number: 28493

=20
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 <=3D param_table.ver_id
       GROUP BY obj_id));

and the following statement would be optimized:

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

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=20
> 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=20
> work but would definitely be ugly.  Can someone think of a better way=20
> to do that ?

I sort of don't see how that hack would be any different from a SRF.=20
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