Hi all,

I have a question about rules. I create a rule to insert data into a
view that works fine,
but when I add a row to the view, PostgreSQL replies INSERT 0 0 if the
row is added and it replies
INSERT oid# 1 if the row is not added.
I have the same behavior with RULES on UPDATE and on DELETE. 

Here the example:


create rule "_RIT_vista" as on insert to vista
    where new.job='SALESMAN'
    do instead
    insert into emp (empno,ename,job) values
(new.empno,new.ename,new.job);
CREATE

insert into vista values ('8900','MANUEL','SALESMAN');
INSERT 0 0        <<<<<<<<<<------------------------- Why it replies
INSERT 0 0 if it adds the row ?
^^^^^^^^^^^

select * from vista;
empno|ename     |job
-----+----------+------------
 8900|MANUEL    |SALESMAN
(1 row)

insert into vista values ('8901','JOSE','PROGRAMMER');
INSERT 144991 1    <<<<<<<<<------------------------- Why it replies
INSERT oid 1 if it adds no row?
^^^^^^^^^^^^^^^

select * from vista;
empno|ename     |job
-----+----------+------------
 8900|MANUEL    |SALESMAN
(1 row)

select oid,* from emp;
   oid|ename     |empno|job         |hiredate|sal|comm|deptno|level|mgr
------+----------+-----+------------+--------+---+----+------+-----+---
144990|MANUEL    | 8900|SALESMAN    |        |   |    |      |     |
(1 row)

Any ideas ?

-Jose'-

Mon, 18 Jan 1999 14:24:41 +0100
"Jose' Soares" <jose@sferacarta.com>
about RULES
>
> Hi all,
>
> I have a question about rules. I create a rule to insert data into a
> view that works fine,
> but when I add a row to the view, PostgreSQL replies INSERT 0 0 if the
> row is added and it replies
> INSERT oid# 1 if the row is not added.
> I have the same behavior with RULES on UPDATE and on DELETE.

    Anything is fine and behaves as (I) expected :-)

    I  know, it's a little irritating. The reply you're seeing is
    the result of the last processed query. Your rule ...

>
> Here the example:
>
>
> create rule "_RIT_vista" as on insert to vista
>     where new.job='SALESMAN'
>     do instead
>     insert into emp (empno,ename,job) values
> (new.empno,new.ename,new.job);
> CREATE

     ... is a conditional one (has  a  rule  qualification  WHERE
    new.job = 'SALESMAN').  In this case, the rule system split's
    your query into one that  inserts  into  "emp"  if  rule-qual
    matched  and  another  that  inserts into "vista" if not. The
    query to insert into "emp" is executed first,  the  one  that
    inserts into "vista" second.

>
> insert into vista values ('8900','MANUEL','SALESMAN');
> INSERT 0 0        <<<<<<<<<<------------------------- Why it replies
> INSERT 0 0 if it adds the row ?
> ^^^^^^^^^^^

    1 row inserted into "emp", 0 rows inserted into "vista".

>
> select * from vista;
> empno|ename     |job
> -----+----------+------------
>  8900|MANUEL    |SALESMAN
> (1 row)
>
> insert into vista values ('8901','JOSE','PROGRAMMER');
> INSERT 144991 1    <<<<<<<<<------------------------- Why it replies
> INSERT oid 1 if it adds no row?
> ^^^^^^^^^^^^^^^

    0 rows inserted into "emp", 1 row inserted into "vista"!

>
> select * from vista;
> empno|ename     |job
> -----+----------+------------
>  8900|MANUEL    |SALESMAN
> (1 row)
>
> select oid,* from emp;
>    oid|ename     |empno|job         |hiredate|sal|comm|deptno|level|mgr
> ------+----------+-----+------------+--------+---+----+------+-----+---
> 144990|MANUEL    | 8900|SALESMAN    |        |   |    |      |     |
> (1 row)
>

    Now  you  wonder  where Postgres left 'JOSE' - eh? He's there
    and all information is kept. But you're  unable  to  see  it,
    because  on  SELECT  from "vista", the rewrite system changes
    your query to something that displays from "emp". But if  you
    take  a look into the database directory, you'll see that the
    file "vista" lost it's zero-size. So there is data!

    You can't update, delete or see him, as long as the _RETvista
    rule  is  active.  Any query that needs to scan "vista" get's
    rewritten to scan from "emp".

    It's a problem of a general-purpose  production  rule  system
    like  the one Postgres has. It does not (and IMHO should not)
    check, whether the summary of all defined rules makes  sense.
    Such a check would make it impossible to setup many qualified
    rules, because they have circular dependencies that cannot be
    matched when you created the first ones.

    In  the case of a qualified instead rule, the parsetree get's
    splitted.  One with the qual, one with the negated  qual.  If
    you  only  want  'SALESMAN's,  you must put the qualification
    into the INSERT action of the rule as

    create rule "_INSvista" as on insert to vista
        do instead
        insert into emp select new.empno, new.ename, new.job
        where new.job='SALESMAN';

    This is an unqualified instead rule, where the ACTION filters
    the  data.  This time, the rewrite system will throw away the
    original query and only the INSERT INTO emp ... is left,  but
    it  will  never  insert  anything than row's of SALESMAN. All
    others are silently thrown away.

    Changing from INSERT ... VALUES to INSERT ... SELECT  is  the
    way  to  make  the  parser  happy  about the WHERE clause. On
    parsetree level, there is no difference between them.

    Well, I expect more questions now - so come on.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Mon, 18 Jan 1999 23:37:03 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] about RULES
> 
>     In  the case of a qualified instead rule, the parsetree get's
>     splitted.  One with the qual, one with the negated  qual.  If
>     you  only  want  'SALESMAN's,  you must put the qualification
>     into the INSERT action of the rule as
> 
>     create rule "_INSvista" as on insert to vista
>         do instead
>         insert into emp select new.empno, new.ename, new.job
>         where new.job='SALESMAN';

I see that rules is more complex than I thougth. 

I tried your example Jan but it doesn't work...

create rule "_INSvista" as on insert to vista
      do instead
      insert into emp select new.empno, new.ename, new.job
      where new.job='SALESMAN';
ERROR:  Type of empno does not match target column ename

-Jose'-

Tue, 19 Jan 1999 13:30:37 +0100
"Jose' Soares" <jose@sferacarta.com>
Re: [HACKERS] about RULES
Jose' Soares ha scritto:

> >
> >     In  the case of a qualified instead rule, the parsetree get's
> >     splitted.  One with the qual, one with the negated  qual.  If
> >     you  only  want  'SALESMAN's,  you must put the qualification
> >     into the INSERT action of the rule as
> >
> >     create rule "_INSvista" as on insert to vista
> >         do instead
> >         insert into emp select new.empno, new.ename, new.job
> >         where new.job='SALESMAN';
>
> I see that rules is more complex than I thougth.
>
> I tried your example Jan but it doesn't work...
>
> create rule "_INSvista" as on insert to vista
>       do instead
>       insert into emp select new.empno, new.ename, new.job
>       where new.job='SALESMAN';
> ERROR:  Type of empno does not match target column ename
>
> -Jose'-

I had no reply to this message probably because a problem with my mail.

I'm trying to make a view updatable using triggers, INSERT works fine
but UPDATE/DELETE
doesn't because the WHERE condition is evaluated FALSE all the time.
Here an example:


create table emp (
        empno    int,
        ename    char(10),
        job      char(12),
        hiredate date,
        sal      money,
        comm     int,
        deptno   int,
        level    int,
        mgr      int
);
CREATE

insert into emp values (8900,'MANUEL','SALESMAN',CURRENT_DATE,'$2,000');

INSERT 149844 1

create table vista
   as select empno, ename, job
      from emp
      where job='SALESMAN';
CREATE

drop function add_vista();
create function add_vista() returns opaque as '
begin
        raise notice ''trigger fired: % on %'',tg_when,tg_op;
        return null;
end;
' language 'plpgsql';


create trigger t_add_vista before insert or update or delete
    on vista for each row execute procedure add_vista();

delete from vista where ename='MANUEL';
NOTICE:  trigger fired: BEFORE on DELETE
DELETE 0
delete from vista ;
NOTICE:  trigger fired: BEFORE on DELETE
DELETE 0

--And now VISTA becames a view:.................

CREATE RULE "_RETvista" AS
  ON SELECT TO "vista"
  DO INSTEAD
  SELECT "empno", "ename", "job"
  FROM "emp" WHERE "job" = 'SALESMAN'::"bpchar";
CREATE

select * from vista;

empno|ename     |job
-----+----------+------------
 8900|MANUEL    |SALESMAN
(1 row)

-- The where condition is all the time evaluated as FALSE...

delete from vista where ename='MANUEL'; --why this condition isn't true
?
DELETE 0

delete from vista ;
NOTICE:  trigger fired: BEFORE on DELETE
DELETE 0


--Is there a way to make views updatable ?

-Jose'-




Fri, 22 Jan 1999 16:33:03 +0100
"Jose' Soares" <jose@sferacarta.com>
Re: [HACKERS] about RULES
> > I tried your example Jan but it doesn't work...
> >
> > create rule "_INSvista" as on insert to vista
> >       do instead
> >       insert into emp select new.empno, new.ename, new.job
> >       where new.job='SALESMAN';
> > ERROR:  Type of empno does not match target column ename
> >
> > -Jose'-
>
> I had no reply to this message probably because a problem with my mail.

    Sorry,

    I  just  wrote  it  down quickly.  Of cause the insert action
    must be a valid statement.  In the above case I  assume,  the
    table  'emp'  didn't had the empno first. When looking at the
    schema of emp you gave below, the rule should read:

        create rule "_INSvista" as on insert to vista
          do instead
          insert into emp (empno, ename, job)
          values (new.empno, new.ename, new.job)
          where new.job = 'SALESMAN';

>
> I'm trying to make a view updatable using triggers, INSERT works fine
> but UPDATE/DELETE
> doesn't because the WHERE condition is evaluated FALSE all the time.
> Here an example:

    A trigger could never work for UPDATE or DELETE. A trigger is
    only  fired  when  there  is actually a row in a table to get
    updated or deleted.  Views  don't  (or  at  least  shouldn't)
    contain any data, so there is never a row to fire them.

>
> create table emp (
>         empno    int,
>         ename    char(10),
>         job      char(12),
>         hiredate date,
>         sal      money,
>         comm     int,
>         deptno   int,
>         level    int,
>         mgr      int
> );
> CREATE
>
> [...]
>
> -- The where condition is all the time evaluated as FALSE...
>
> delete from vista where ename='MANUEL'; --why this condition isn't true
> ?
> DELETE 0

    The  rule  system  redirected  the  scan  for the DELETE from
    'vista' to a scan from 'emp' because vista is a view on  emp.
    The  resulting  query  is a scan from emp who's result tupels
    should be deleted from vista - a whole  lot  of  nonsens  and
    thus absolutely nothing happens.

>
> delete from vista ;
> NOTICE:  trigger fired: BEFORE on DELETE
> DELETE 0
>
>
> --Is there a way to make views updatable ?

    Read  section  8  of the programmers manual to understand all
    the details of the rewrite  rule  system.  Then  do  it  with
    rules.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Fri, 22 Jan 1999 20:03:16 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] about RULES