Date: Thu, 28 Oct 2004 12:03:20 +0200
From: Markus Schaber <schabios@logi-track.com>
To: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Select Instead on a table
Message-ID: <20041028120320.4445314b@kingfisher.intern.logi-track.com>
Organization: logi-track ag, =?ISO-8859-15?Q?z=FCrich?=
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable

Hello,

[I hope this is no FAQ, but I did neither find anything about it in the
PostgreSQL FAQ, nor get any hit on RULE or INSTEAD on the mailing list
archive search=B9...]

Is it possible to create a ON SELECT DO INSTEAD rule on a table?

All of my tries to do so failed by either complaining that the table is
not empty, or converting the empty table into a view, thus making
insert/update/delete impossible.

What we need is basically a table "outer" where one column is computed
via a subselect over another table "inner", something like

SELECT id, some, more, rows, =20
    ( SELECT aggregate(inner.innerrow) AS collect=20
        WHERE inner.id=3Douter.id) as INNER
    FROM outer;

(In reality, the inner query is a little more complicated because of
some weird ordering and limit stuff, but this is the basic idea)

Of yourse, we could (and currently do) do this via a View, but this has
two disadvantages:

- We have an extra relation floating around (outer and outer_view)

- We need to create and maintain INSERT/UPDATE/DELETE rules for outer.

We cannot change the application code that issues the queries, this is
the reason for dealing with rules and views in this case.

Thanks,
Markus Schaber

Footnotes:=20
=B9 The latter was rather surprising to me, may be a defect in the mailing=
=20
  list archive search?

--=20
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 z=FCrich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com