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
>