Re: Why does an ON SELECT rule have to be named "_RETURN"? - Mailing list pgsql-general

On Sun, 12 Feb 2006, Ken Winter wrote:

> > -----Original Message-----
> > From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
> > Sent: Sunday, February 12, 2006 8:47 PM
> > To: Ken Winter
> > Cc: 'Tom Lane'; 'PostgreSQL pg-general List'
> > Subject: Re: [GENERAL] Why does an ON SELECT rule have to be named
> > "_RETURN"?
> >
> > On Sun, 12 Feb 2006, Ken Winter wrote:
> >
> > > Hi Tom ~
> > >
> > > You're right:  I appealed to the PostgreSQL folks rather than the client
> > > tool builders.  I did so because my guess is that the latter have a
> > harder
> > > row to hoe:  They have to figure out whether a view really IS updatable
> > -
> > > most presumably aren't, so if they provide forms that offer to update
> > views,
> > > most of the time these forms are going to crash.  It seems harder for
> > the
> > > client tool builders to figure out the updatability question than for
> > > PostgreSQL to let people (like me) do the "real table with ON SELECT"
> > trick
> > > and take responsibility for making it work.  I don't see why that is
> > > inherently "broken".
> >
> > What does a "real table with ON SELECT" mean?
>
> It means a table that, due to the rules on it, works exactly like a view
> (from the client's perspective).  (Here, let me call it a view-table.)  No
> row ever gets inserted into the view-table.  The rules deflect inserts into
> one or more base tables.  Updates and deletes, though from the client's view
> they modify or remove rows in the view-table, actually update and delete in
> the underlying base tables.

How is this different from a view with on insert, on update and on delete
rules right now?

> > For example, if a row is
> > "inserted" that doesn't come into the on select output, was a row
> > inserted?
>
> In what I'm doing, that would not happen.  But there might be a case where
> someone would want a design where rows inserted through the view-table,
> though they do get inserted into the underlying base tables, would not be
> visible through SELECT actions on the view-table.  I can't imagine offhand
> why anyone would want to do this, but I don't see why PostgreSQL should stop
> them.  (...Actually, on second thought, I have thought of doing a trick like
> this myself, to get around the PostgreSQL constraint I'm complaining about:
> Define a view-table with all of the update rules on it, so no rows ever get
> inserted into it but my client tools can do updates against it; then define
> a second, read-only, view for SELECTs to reveal the data entered through the
> first view.  Right; I would rather not stoop to this.)
>
> > Can it cause unique key violations, can it satisfy a foreign key
> > constraint?
>
> PK, UK, FK, and check constraints would all be defined on the base tables,
> not on the view-table.  So actions on the view-table would satisfy or
> violate these constraints, like any other actions redirected through
> PostgreSQL update rules.

But then this "view-table" isn't really a real table. If it's not a
real table, it pretty much defeats the original stated argument of having
"real tables with on select rules".