Re: Problems using a rule with the WHERE clause

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 <llsousa(at)ualg(dot)pt> 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