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

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(at)sunward(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)sunward(dot)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(at)sunward(dot)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(at)sunward(dot)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(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Sunday, February 12, 2006 6:43 PM
> To: ken(at)sunward(dot)org
> Cc: 'PostgreSQL pg-general List'
> Subject: Re: [GENERAL] Why does an ON SELECT rule have to be named
> "_RETURN"?
>
> "Ken Winter" <ken(at)sunward(dot)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(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

On Sun, 12 Feb 2006, Ken Winter wrote:

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

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(at)megazone(dot)bigpanda(dot)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(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.
>
> 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(at)svana(dot)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.

Martijn van Oosterhout <kleptog(at)svana(dot)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(at)svana(dot)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(at)users(dot)sourceforge(dot)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