Thread: new rule syntax?

I have two tables defined as:
checks       (ckid                int NOT null PRIMARY KEY,        payto               text,        notes
text,       ckdate              date,        printed             int  default 0,        tdate               timestamp
notnull)
 
checkitems       (item                int not null,        ckid                int NOT null references checks,
itemtype           int not null,        amt                 numeric(7,3),        primary key         (item, ckid))
 

in previous versions (<8.1) the following rule declaration seemed to 
work finecreate rule checks_d0 as    on delete to checks       do delete from checkitems          where ckid =
checks.ckid;
in 8.1.2 I get

ERROR: missing FROM-clause entry from table "checks"

any idea?

-- 
Arthur R. Van Hook     Mayor 
The City of Lake Lotawana

hook@lake-lotawana.mo.us

(816) 578-4704 - Home
(816) 578-4215 - City
(816) 564-0769 - Cell

am  05.02.2006, um  6:00:18 -0600 mailte A. R. Van Hook folgendes:
> in previous versions (<8.1) the following rule declaration seemed to work 
> fine
>  create rule checks_d0 as
>      on delete to checks
>         do delete from checkitems
>            where ckid = checks.ckid;
> in 8.1.2 I get
> 
> ERROR: missing FROM-clause entry from table "checks"

*untested*:

create rule checks_d0 ason delete to checks    do delete from checkitems, checks        where checkitems.ckid =
checks.ckid;

Read please:
http://www.postgresql.org/docs/8.1/interactive/release-8-1.html

,----
|  add_missing_from is now false by default (Neil)
|
| By default, we now generate an error if a table is used in a query
| without a FROM reference. The old behavior is still available, but the
| parameter must be set to 'true' to obtain it.
`----


HTH, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    === 

A. R. Van Hook написа:
> I have two tables defined as:
> checks
>        (ckid                int NOT null PRIMARY KEY,
>         payto               text,
>         notes               text,
>         ckdate              date,
>         printed             int  default 0,
>         tdate               timestamp not null)
> checkitems
>        (item                int not null,
>         ckid                int NOT null references checks,
>         itemtype            int not null,
>         amt                 numeric(7,3),
>         primary key         (item, ckid))
> 
> in previous versions (<8.1) the following rule declaration seemed to 
> work fine
> create rule checks_d0 as
>     on delete to checks
>        do delete from checkitems
>           where ckid = checks.ckid;
> in 8.1.2 I get
> 
> ERROR: missing FROM-clause entry from table "checks"
> 
> any idea?

May be you are bitten by the change of the default value of 
"add_missing_from" setting 
(http://www.postgresql.org/docs/8.1/static/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION).


-- 
Milen A. Radev

Try this rule instead
create rule checks_d0 as    on delete to checks       do delete from checkitems          where ckid = OLD.ckid;


---------- Original Message -----------
From: "Milen A. Radev" <milen@radev.net>
To: pgsql-sql@postgresql.org
Sent: Sun, 05 Feb 2006 15:10:23 +0200
Subject: Re: [SQL] new rule syntax?

[UTF-8?]> A. R. Van Hook написа:
> > I have two tables defined as:
> > checks
> >        (ckid                int NOT null PRIMARY KEY,
> >         payto               text,
> >         notes               text,
> >         ckdate              date,
> >         printed             int  default 0,
> >         tdate               timestamp not null)
> > checkitems
> >        (item                int not null,
> >         ckid                int NOT null references checks,
> >         itemtype            int not null,
> >         amt                 numeric(7,3),
> >         primary key         (item, ckid))
> > 
> > in previous versions (<8.1) the following rule declaration seemed to 
> > work fine
> > create rule checks_d0 as
> >     on delete to checks
> >        do delete from checkitems
> >           where ckid = checks.ckid;
> > in 8.1.2 I get
> > 
> > ERROR: missing FROM-clause entry from table "checks"
> > 
> > any idea?
> 
> May be you are bitten by the change of the default value of 
> "add_missing_from" setting 
> (http://www.postgresql.org/docs/8.1/static/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION).
> 
> -- 
> Milen A. Radev
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
------- End of Original Message -------

A. R. Van Hook wrote:
> I have two tables defined as:
> checks
>        (ckid                int NOT null PRIMARY KEY,
>         payto               text,
>         notes               text,
>         ckdate              date,
>         printed             int  default 0,
>         tdate               timestamp not null)
> checkitems
>        (item                int not null,
>         ckid                int NOT null references checks,
>         itemtype            int not null,
>         amt                 numeric(7,3),
>         primary key         (item, ckid))
> 
> in previous versions (<8.1) the following rule declaration seemed to 
> work fine

Others have addresses your query with the rule. Can I ask why you're not 
using a DELETE CASCADE on the fkey?
...
ckid    int NOT null references checks ON DELETE CASCADE,
...

--   Richard Huxton  Archonet Ltd