Re: stumped on view/rule/delete problem.

Hmm, what about this instead?


create table foo (
  id serial primary key,
  name text
);


create table bar (
  foo_id integer NOT NULL REFERENCES foo ON DELETE CASCADE,
  name2 text
);

create view foobar as
  select f.id, f.name, b.name2 from foo f, bar b where (f.id = b.foo_id);

create rule delete_foobar as on delete to foobar do instead delete from foo
where id = OLD.id;


(works on v7.1.2)

- Andrew



On Mon, 11 Jun 2001, Pete Leonard wrote:

>
> Hey folks,
>
> Still learning the ins and outs of postgres, and I'm stumped on this
> problem - was hoping someone here could help.
>
> create table foo (
> id    serial,
> name    varchar(50)
> );
>
> create table bar (
> foo_id    integer,
> name2    varchar(50)
> );
>
> create view foobar as
> select f.id, f.name, b.name2 from foo f, bar b where (f.id = b.foo_id);
>
>
> now -
>
> create rule delete_foobar as on delete to foobar
> do instead (
> delete from foo where id=OLD.id;
> delete from bar where foo_id = OLD.id;
> );
>
> running the command
> delete from foobar where id=1;
>
> causes the DB to hang.  only way out is an immediate restart of the DB.
>
> Needless to say, this isn't the functionality I was looking for.  :)
>
> Postgres v. 7.1, running under FreeBSD.
>
>     thanks,
>
>     --pete
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>