Thread: NEW variable values in trigger functions
1. What is the value of the NEW variable for a column that is not mentioned in an UPDATE statement? Is it NULL? If not NULL, what? For example, given this table: my_tbl (id integer, att1 varchar, att2 varchar) and a row-wise ON UPDATE OR INSERT trigger function containing this conditional: IF NEW.att2 IS NULL THEN <do stuff> END IF; and this UPDATE query: UPDATE my_tbl SET att1 = 'foo' where id = 1; will that conditional be satisfied? 2. Same questions re the value of a NEW variable that is not assigned a value in an INSERT statement. For example, how would the previous conditional behave in response to: INSERT INTO my_tbl (id) VALUES (1); ? 3. If an UPDATE query set a column to DEFAULT, what value does a trigger function see for the column's NEW variable? Is it the string 'DEFAULT', a reserved word DEFAULT, an empty string, or what? For example, what would you put in place of <??> in this UPDATE trigger function: IF NEW.att2 <??> THEN <do stuff> END IF; to get it to <do stuff> in response to this UPDATE query: UPDATE my_tbl SET att2 = DEFAULT where id = 1; ? ~ TIA ~ Ken
"Ken Winter" <ken@sunward.org> writes: > 1. What is the value of the NEW variable for a column that is not mentioned > in an UPDATE statement? Is it NULL? If not NULL, what? No, it's whatever value is going to be assigned to the new row (which in this particular case would be the same as the OLD value). > 2. Same questions re the value of a NEW variable that is not assigned a > value in an INSERT statement. Same answer: whatever value is due to go into the row (in this case, whatever the default is for the column). > 3. If an UPDATE query set a column to DEFAULT, what value does a trigger > function see for the column's NEW variable? Same answer. regards, tom lane
Tom ~ Your answers (below) to my questions about the values of NEW columns in trigger functions was very clarifying for me (as well as being correct!). Now I'm having similar problems with NEW values in actions that are embedded in rules, and the answers don't seem to be the same. I have a table "person_h" with a not-null column "effective_date_and_time" that defaults to CURRENT_TIMESTAMP. I have a view "person" with the following rule defined on it: CREATE RULE on_insert AS ON INSERT TO person DO INSTEAD ( INSERT INTO person_i (person_id, birth_date) VALUES (nextval('pop_seq'::text), new.birth_date); INSERT INTO person_h (person_id, ... effective_date_and_time, ...) VALUES (currval('pop_seq'::text), last_name, ... new.effective_date_and_time, new.last_name, ...); where the "..."s are some other columns not of interest here. So now when I do this query: insert into public.person (last_name) values ('Jones'); I get this error: PostgreSQL Error Code: (1) ERROR: null value in column "effective_date_and_time" violates not-null constraint--0 Rows Affected So the query processor seems not to be behaving as you described in answering my question 2 below: it is not taking the NEW.effective_date_and_time value to be "whatever the default is for the column". If I submit the same "INSERT INTO person_h..." query directly, rather than as part of the rule, it works fine. Help? ~ TIA ~ Ken > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Thursday, January 05, 2006 7:49 PM > To: Ken Winter > Cc: PostgreSQL pg-general List > Subject: Re: [GENERAL] NEW variable values in trigger functions > > "Ken Winter" <ken@sunward.org> writes: > > 1. What is the value of the NEW variable for a column that is not > mentioned > > in an UPDATE statement? Is it NULL? If not NULL, what? > > No, it's whatever value is going to be assigned to the new row (which in > this particular case would be the same as the OLD value). > > > 2. Same questions re the value of a NEW variable that is not assigned a > > value in an INSERT statement. > > Same answer: whatever value is due to go into the row (in this case, > whatever the default is for the column). > > > 3. If an UPDATE query set a column to DEFAULT, what value does a trigger > > function see for the column's NEW variable? > > Same answer. > > regards, tom lane
"Ken Winter" <ken@sunward.org> writes: > I have a table "person_h" with a not-null column "effective_date_and_time" > that defaults to CURRENT_TIMESTAMP. > I have a view "person" with the following rule defined on it: > CREATE RULE on_insert AS > ON INSERT TO person > DO INSTEAD ( > INSERT INTO person_h (person_id, ... > effective_date_and_time, ...) You need to provide a column default on the view; the one on the underlying table would only enter into the picture if the "INSERT INTO person_h" command in the rule omitted specifying effective_date_and_time, which it does not. Use ALTER TABLE person ALTER COLUMN ... SET DEFAULT ... to attach a default to the view. regards, tom lane
I'm trying to build something that behaves like an updatable view but that PostgreSQL (version 7.4) regards and presents to the world as a table. The reason I want to do this odd thing is that my front-end tools (phpPgAdmin and PostgreSQL Lightning Admin) have handy pre-made data entry and viewing forms, but they only work against tables (not against views). The PostgreSQL documentation (http://www.postgresql.org/docs/7.4/static/rules-views.html) says that: "There is essentially no difference between CREATE VIEW myview AS SELECT * FROM mytab; compared against the two commands CREATE TABLE myview (same column list as mytab); CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD SELECT * FROM mytab; because this is exactly what the CREATE VIEW command does internally." OK, I figured, so if I turn my existing view (made updatable by suitable ON INSERT, ON UPDATE, and ON DELETE rules), which works fine, into a table with an ON SELECT rule on the above pattern, that ought to work. But I decided to name my ON SELECT rule something other than "_RETURN", so PostgreSQL wouldn't suspect what I was up to. Alas, PostgreSQL responded with an error message saying that a "view rule...must be named "_RETURN"'. When I renamed it thus, PostgreSQL accepted the whole thing - but ended up classifying the resulting structure as a view, which defeated my purpose of making it accessible through my front-end tools. So I'm wondering: * Why this constraint? * Would anything break if I were allowed to get away with my little trick? * Is there any way to get around the constraint? ~ TIA ~ Ken
"Ken Winter" <ken@sunward.org> writes: > * Why this constraint? > * Would anything break if I were allowed to get away with my little trick? > * Is there any way to get around the constraint? The reason why the table is converted to a view is that ancient pg_dump dumps used to create views in exactly that way (make a table and then add an ON SELECT rule) and so when we started making a hard distinction between tables and views, we needed to force the conversion to occur. The notion of a real table that has an ON SELECT rule seems fairly broken to me in any case. I think you should be complaining to the authors of your client-side tools that they won't do what you want. It would probably be quite a trivial change to get them to support data entry forms against views, but changing the backend on this point won't be an easy sell. regards, tom lane
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". Everybody from E.F. Codd onward has struggled with the view updatability problem; it seems like PostgreSQL is one unnecessary constraint away from letting users find a pragmatic solution (a.k.a. workaround) for it. ~ Ken > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Sunday, February 12, 2006 6:43 PM > To: ken@sunward.org > Cc: 'PostgreSQL pg-general List' > Subject: Re: [GENERAL] Why does an ON SELECT rule have to be named > "_RETURN"? > > "Ken Winter" <ken@sunward.org> writes: > > * Why this constraint? > > * Would anything break if I were allowed to get away with my little > trick? > > * Is there any way to get around the constraint? > > The reason why the table is converted to a view is that ancient pg_dump > dumps used to create views in exactly that way (make a table and then > add an ON SELECT rule) and so when we started making a hard distinction > between tables and views, we needed to force the conversion to occur. > > The notion of a real table that has an ON SELECT rule seems fairly > broken to me in any case. I think you should be complaining to the > authors of your client-side tools that they won't do what you want. > It would probably be quite a trivial change to get them to support > data entry forms against views, but changing the backend on this > point won't be an easy sell. > > regards, tom lane
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? For example, if a row is "inserted" that doesn't come into the on select output, was a row inserted? Can it cause unique key violations, can it satisfy a foreign key constraint?
Ken, Why don't you tell us why you need updateable views? What exactly are you trying to accomplish? (you may have already mentioned it,but I must have missed it :-) Why don't you create a set returning function and then have the client do the updating? i.e. have another function to update the data when the client changes the data. Maybe this is not that easy for a web interface, but in Delphi or Lazarus using the tdataset it's a piece of cake. Also what about using a .net disconnected dataset? I can honestly say I have never had the need for a updatable view in any application I have ever created (thin or fat client). Maybe you just need to look at alternative application designs that don't need a updatable view. Thanks, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com
> -----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. > 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
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".
Ken Winter wrote: > 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. First of all, it isn't all that hard to figure out whether a view is probably updatable (the presence of the respective rules would be a strong hint). And second, if it's not, you get a fine error message and can move on. So there is really no good reason for client tools to prevent you from using data entry forms on views. -- Peter Eisentraut http://developer.postgresql.org/~petere/
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".
Ken Winter wrote: >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"). > > Ken, In PGLA you can use the quick view data option on a view, and if the view is updatable the grid should work as well as the form view. The Quick View Data option does not really know if it is a table or a view. I just tried it and without the insert,update and delete rules it raises a error with a hint that you should add the rules. Why don't you try that? I can add a data tab to the view editor if that works for you. Thanks, Tony
On Mon, 13 Feb 2006, Ken Winter wrote: > 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 problem is that you're not just punning the type to the client. You're punning the type to the server. Your "view-table" will be a table, even for operations that might not work because it's really a view, and the code isn't going to know to not allow it. If everything we had that works for ordinary tables worked for views, it wouldn't be a problem, but AFAIK that's not true. > 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). Well, it implies that create view and create table + create rule ... on select are equivalent, but I'd agree that this could probably be better documented. > 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? Apart from modifying the code, I don't think so.
On Monday 13 February 2006 01:49, Peter Eisentraut wrote: > Ken Winter wrote: > > 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. > > First of all, it isn't all that hard to figure out whether a view is > probably updatable (the presence of the respective rules would be a > strong hint). And second, if it's not, you get a fine error message > and can move on. So there is really no good reason for client tools to > prevent you from using data entry forms on views. One problem is the only way for a client tool to work generically in provding data entry forms would be to provide entry for all columns, which would break in all but the most trivial of cases. Last time we discussed this for phppgadmin, the general opinion was it wasn't worth trying to work around postgresql core's deficiency. Once the core postgresql server supports updatable views in proper, I'd imagine this would get done. -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On Thu, Feb 16, 2006 at 07:28:20AM -0500, Robert Treat wrote: > On Monday 13 February 2006 01:49, Peter Eisentraut wrote: > > First of all, it isn't all that hard to figure out whether a view is > > probably updatable (the presence of the respective rules would be a > > strong hint). And second, if it's not, you get a fine error message > > and can move on. So there is really no good reason for client tools to > > prevent you from using data entry forms on views. > > One problem is the only way for a client tool to work generically in provding > data entry forms would be to provide entry for all columns, which would break > in all but the most trivial of cases. Last time we discussed this for > phppgadmin, the general opinion was it wasn't worth trying to work around > postgresql core's deficiency. Once the core postgresql server supports > updatable views in proper, I'd imagine this would get done. True, but the client should allow it (possibly with a warning). Not allowing people to generate forms on a view just because it may not be updatable seems silly... There's plenty of info on how to create updatable views on PostgreSQL, allowing users to indicate themselves which fields are updatable seems better than trying to guess from the database. In the general case, if there are any INSERT/UPDATE/DELETE RULEs on a view, there is no way for the client to determine what the effect will be except in the simplest of cases, letting the user specify seems the best bet. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes: > On Thu, Feb 16, 2006 at 07:28:20AM -0500, Robert Treat wrote: >> One problem is the only way for a client tool to work generically in prov= > ding >> data entry forms would be to provide entry for all columns, which would b= > reak >> in all but the most trivial of cases. Last time we discussed this for >> phppgadmin, the general opinion was it wasn't worth trying to work around= > >> postgresql core's deficiency. Once the core postgresql server supports >> updatable views in proper, I'd imagine this would get done. > In the general case, if there are any INSERT/UPDATE/DELETE RULEs on a > view, there is no way for the client to determine what the effect will > be except in the simplest of cases, letting the user specify seems the > best bet. I agree that this decision on phppgadmin's part seems unsupportable. Either there is an ON UPDATE rule on a view or there isn't --- it is not phppgadmin's job to determine what cases that rule supports. Try to do the update, and complain if it fails, is all that is required from a client-side tool. regards, tom lane
On Thu, 2006-02-16 at 10:41, Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: > > On Thu, Feb 16, 2006 at 07:28:20AM -0500, Robert Treat wrote: > >> One problem is the only way for a client tool to work generically in prov= > > ding > >> data entry forms would be to provide entry for all columns, which would b= > > reak > >> in all but the most trivial of cases. Last time we discussed this for > >> phppgadmin, the general opinion was it wasn't worth trying to work around= > > > >> postgresql core's deficiency. Once the core postgresql server supports > >> updatable views in proper, I'd imagine this would get done. > > > In the general case, if there are any INSERT/UPDATE/DELETE RULEs on a > > view, there is no way for the client to determine what the effect will > > be except in the simplest of cases, letting the user specify seems the > > best bet. > > I agree that this decision on phppgadmin's part seems unsupportable. > Either there is an ON UPDATE rule on a view or there isn't --- it is not > phppgadmin's job to determine what cases that rule supports. Try to do > the update, and complain if it fails, is all that is required from a > client-side tool. > This is semi-orthogonal, but I'd hoped that with first-class updatable views we might get some method to determine which columns are actually updatable, but perhaps this is just wishful thinking? Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Robert Treat <xzilla@users.sourceforge.net> writes: > This is semi-orthogonal, but I'd hoped that with first-class updatable > views we might get some method to determine which columns are actually > updatable, but perhaps this is just wishful thinking? All of them I should think. I certainly don't see us automatically creating update rules for any cases where this isn't so. regards, tom lane