pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (6+ messages)  latest in thread

Message-ID: <3B385B5E.A8A31C59@ualg.pt>
Permalink:  ../3B385B5E.A8A31C59@ualg.pt/
Also on:    postgresql.org/message-id/3B385B5E.A8A31C59@ualg.pt

 · 

reply

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