On Sun, 12 Feb 2006, Ken Winter wrote: > > -----Original Message----- > > From: Stephan Szabo [mailto:sszabo@xxxxxxxxxxxxxxxxxxxxx] > > 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".
