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

Stephan ~

You're right: This thing I call a "view-table" would behave *exactly* like a
view that has insert, update, and delete rules.

The *only* difference I'm trying to achieve is to get it stored in
pg_catalog.pg_class with relkind = 'r' ("ordinary table") rather than 'v'
("view").

The *only* reason I'm trying to disguise a view as a table is to trick my
client tools into letting me use their handy pre-made forms and grids to
read and write to these structures.

The reason I'm trying to activate these forms and grids is to enable my
testing users to start entering and viewing test data immediately - without
their having to learn and write SQL, and without my having to build data
entry and review forms for them.

I thought, all things considered, my little trick - admittedly a workaround
- would be the easiest way to achieve what I need without requiring anything
of either the PostgreSQL architects or the tool builders.  So it is
frustrating to be defeated by this one PostgreSQL constraint (which isn't
even published in the documentation, as far as I can see).

I just had another workaround idea - declare the "view-table" as an ordinary
table and put triggers on it that implement the functionality of the rules -
but before resorting to that I thought I'd try my simpler trick once more.

So let me ask again: Is there any way to disable this constraint that forces
the SELECT rule to be named "_RETURN"?  Or is there any other way to
accomplish what I'm trying to do?

~ Thanks again
~ Ken

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
> Sent: Sunday, February 12, 2006 11:39 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:
>
> > > -----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".