pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect Instead on a table
3+ messages / 2 participants
[nested] [flat]
* Select Instead on a table
@ 2004-10-28 10:03 Markus Schaber <schabios@logi-track.com>
0 siblings, 1 reply; 3+ messages in thread
From: Markus Schaber @ 2004-10-28 10:03 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Select Instead on a table
@ 2004-10-28 14:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Markus Schaber <schabios@logi-track.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-10-28 14:22 UTC (permalink / raw)
To: Markus Schaber <schabios@logi-track.com>; +Cc: pgsql-sql
Markus Schaber <schabios@logi-track.com> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Select Instead on a table
@ 2004-10-28 16:58 Markus Schaber <schabios@logi-track.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Markus Schaber @ 2004-10-28 16:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Hi, Tom,
On Thu, 28 Oct 2004 10:22:47 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Is it possible to create a ON SELECT DO INSTEAD rule on a table?
>
> Not unless you want it to become a view.
That's what I suspected, after scanning the docs.
Thanks,
Markus
--
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-10-28 16:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-28 10:03 Select Instead on a table Markus Schaber <schabios@logi-track.com>
2004-10-28 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-28 16:58 ` Markus Schaber <schabios@logi-track.com>
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