Re: Why does an ON SELECT rule have to be named "_RETURN"?

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo(at)megazone(dot)bigpanda(dot)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.

> 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.

~ Ken