[SQL] new rule syntax?

2006-02-05 Thread A. R. Van Hook 

I have two tables defined as:
checks
   (ckidint NOT null PRIMARY KEY,
payto   text,
notes   text,
ckdate  date,
printed int  default 0,
tdate   timestamp not null)
checkitems
   (itemint not null,
ckidint NOT null references checks,
itemtypeint 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?

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


[EMAIL PROTECTED]

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


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] new rule syntax?

2006-02-05 Thread A. Kretschmer 
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 as
on 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=== 

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] new rule syntax?

2006-02-05 Thread Milen A. Radev 

A. R. Van Hook написа:

I have two tables defined as:
checks
   (ckidint NOT null PRIMARY KEY,
payto   text,
notes   text,
ckdate  date,
printed int  default 0,
tdate   timestamp not null)
checkitems
   (itemint not null,
ckidint NOT null references checks,
itemtypeint 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 [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] new rule syntax?

2006-02-05 Thread Jim Buttafuoco 

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" <[EMAIL PROTECTED]>
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
> >(ckidint NOT null PRIMARY KEY,
> > payto   text,
> > notes   text,
> > ckdate  date,
> > printed int  default 0,
> > tdate   timestamp not null)
> > checkitems
> >(itemint not null,
> > ckidint NOT null references checks,
> > itemtypeint 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 [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
--- End of Original Message ---

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Function Dependency

2006-02-05 Thread Tony Wasson 
On 2/1/06, Padam J Singh <[EMAIL PROTECTED]> wrote:
>  Hello,
>
>  I am maintaining an application that has over 400 procedures and functions
> written in plsql, and around 100 tables.
>  I want to generate a function dependency chart to depict the following:
>
>  1. Inter function/procedure dependencies
>  2. function-tables dependencies
>  3. function-sequences depencies
>
>  Is there a standard method of doing this?
>
>  Thanks in advance,
>  Padam.

I too would be interested in a standardized tool to do this. I had a
similar situation and I ended up writing a perl script to parse my SQL
and make a graphviz dot file. I then used graphviz to make a function
dependency chart. I can't promise it would catch every single case,
but I can provide you with the code if you wish to give it a whirl.

Tony Wasson

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org