Received: from mozart.si.ualg.pt (mozart.si.ualg.pt [193.136.224.2]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5Q9qqa58142 for ; Tue, 26 Jun 2001 05:52:52 -0400 (EDT) (envelope-from llsousa@ualg.pt) Received: from ualg.pt (llsousa@corisco.uceh.ualg.pt [10.10.20.126]) by mozart.si.ualg.pt (8.9.1/8.9.1) with ESMTP id KAA68881; Tue, 26 Jun 2001 10:52:30 +0100 (WEST) Message-ID: <3B385B5E.A8A31C59@ualg.pt> Date: Tue, 26 Jun 2001 10:52:30 +0100 From: Luis Sousa Organization: Universidade do Algarve X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.2.17-compact i686) X-Accept-Language: en, pt MIME-Version: 1.0 To: Tom Lane CC: pgsql sql Mailing List Subject: Re: Problems using a rule with the WHERE clause References: <3B3753FB.2E1BA9B0@ualg.pt> <16743.993492071@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200106/410 X-Sequence-Number: 3707 Tom, I wrote the rule as you mentioned and I resolve that problem, but after some tests I realize that the system wasn't following the condition inside the WHERE clause, to determine which rule to use There's an example used: CREATE RULE "updateturnodocente_default" AS ON UPDATE TO "docentesturno" DO INSTEAD NOTHING; CREATE RULE "updateturnodocente_all" AS ON UPDATE TO "docentesturno" WHERE OLD.idpessoal = 338 DO INSTEAD ( UPDATE "pessoalEvento" SET ponderacao = NEW.ponderacao, envolvimento = NEW.envolvimento WHERE id_pe = OLD.id_pe; ); CREATE RULE "updateturnodocente" AS ON UPDATE TO "docentesturno" WHERE OLD.idpessoal = 0 DO INSTEAD ( UPDATE "pessoalEvento" SET "idPessoal" = NEW."idpessoal", ponderacao = NEW.ponderacao, envolvimento = NEW.envolvimento WHERE id_pe = OLD.id_pe; ); I tried with the first two rules and it worked, but when I add the third, I got an error. I think this code is OK !!!!! But I got the error message bellow in line 11 of my script, that corresponds to the rule updateturnodocente_default psql:dev/pessoal/def/views_rules/teste_update:11: pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. psql:dev/pessoal/def/views_rules/teste_update:11: connection to server was lost What can be happening ??? Best regards Luis Sousa Tom Lane wrote: > Luis Sousa writes: > > CREATE RULE "updateturnodocente" AS ON UPDATE TO "docentesturno" > > WHERE OLD.idpessoal != 0 > > DO INSTEAD ( > > ... > > > When I execute the INSERT into de view docentesturno I got the message: > > ERROR: Cannot update a view without an appropriate rule > > You failed to supply a rule covering the case OLD.idpessoal = 0. > > More specifically, you *must* supply an unconditional INSTEAD rule to > replace the attempt to insert/update in the view. Possibly what you > want is > > CREATE RULE "updateturnodocente" AS ON UPDATE TO "docentesturno" > WHERE OLD.idpessoal != 0 > DO ( ... ); > > CREATE RULE "updateturnodocente_default" AS ON UPDATE TO "docentesturno" > DO INSTEAD NOTHING; > > Here, the unconditional rule always fires, and the conditional one fires > only when its condition is true. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster