Select Instead on a table

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,
( 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ürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: Select Instead on a table

Markus Schaber writes:
> Is it possible to create a ON SELECT DO INSTEAD rule on a table?

Not unless you want it to become a view.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Select Instead on a table

Hi, Tom,

On Thu, 28 Oct 2004 10:22:47 -0400
Tom Lane wrote:

> > Is it possible to create a ON SELECT DO INSTEAD rule on a table?
>=20
> Not unless you want it to become a view.

That's what I suspected, after scanning the docs.

Thanks,
Markus

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster