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