From: Markus Schaber <schabios@logi-track.com>
To: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Select Instead on a table
Date: Thu, 28 Oct 2004 12:03:20 +0200
Message-ID: <20041028120320.4445314b@kingfisher.intern.logi-track.com> (raw)
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¹...]
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
WHERE inner.id=outer.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:
¹ The latter was rather surprising to me, may be a defect in the mailing
list archive search?
--
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: schabios@logi-track.com
Subject: Re: Select Instead on a table
In-Reply-To: <20041028120320.4445314b@kingfisher.intern.logi-track.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox