From: Luis Sousa <llsousa@ualg.pt>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql sql Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Problems using a rule with the WHERE clause
Date: Tue, 26 Jun 2001 10:52:30 +0100
Message-ID: <3B385B5E.A8A31C59@ualg.pt> (raw)
References: <3B3753FB.2E1BA9B0@ualg.pt>
<16743.993492071@sss.pgh.pa.us>
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@ualg.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: llsousa@ualg.pt, tgl@sss.pgh.pa.us
Subject: Re: Problems using a rule with the WHERE clause
In-Reply-To: <3B385B5E.A8A31C59@ualg.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox