Обсуждение: Rule Error

Hengky Lie wrote:
> 
> I have 2 tables : tblmasdbt and tblmasgl. 
> 
> I want on every record insertion in tblmasdbt, that record also
> automatically insert into tblmasdbt. I need only 2 related field.

You probably want triggers rather than rules, but anyway.

> CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt" 
> DO (insert into tblmasgl (KODEGL,NAMAREK) VALUES (new.KODEGL, new.NAMAREK));

> But I always get this error :
> 
> ERROR:  column "kodegl" of relation "tblmasgl" does not exist

There is not a column called kodegl on table tblmasgl.

> Here is the Table Structure 

> CREATE TABLE "public"."tblmasgl" (
>   "KODEGL" VARCHAR(15) NOT NULL, 

There you go - you double-quoted the column-name when creating the 
table. That means that it is literally "KODEGL" and will not match 
kodegl or KoDeGl or any other combination of upper and lower case.

If you double-quote column-names when you create a table you'll want to 
double-quote them every time you use them too.

--   Richard Huxton  Archonet Ltd

am  Thu, dem 04.10.2007, um 19:22:32 +0800 mailte Hengky Lie folgendes:
> CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt"
> 
> DO (insert into tblmasgl (KODEGL,NAMAREK) VALUES (new.KODEGL, new.NAMAREK));
> 
>  
> 
> But I always get this error :
> 
> 
> ERROR:  column "kodegl" of relation "tblmasgl" does not exist
> 
> 
> Here is the Table Structure
> 
> CREATE TABLE "public"."tblmasgl" (
> 
>   "KODEGL" VARCHAR(15) NOT NULL,

Okay, you need to quote the column-name with " since they are in
uppercase.



DO (insert into tblmasgl ("KODEGL","NAMAREK") and maybe also
new."KODEGL" and new."NAMAREK".


Try it and tell if you have success.



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Yes, it works now ! Wow, the problem is in the field name. Changed it to
lowercase solved the problem. Thank you to all ho give me this advice.

But now I have another question regarding to this field, what command I can
use in UPDATE RULE to make these 2 fields (KODEGL and NAMAREK) keep syncron
between these 2 tables (tblmasdbt and tblmasgl) ?

Thank you so much.

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of A. Kretschmer
Sent: 04 Oktober 2007 21:00
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Rule Error

am  Thu, dem 04.10.2007, um 19:22:32 +0800 mailte Hengky Lie folgendes:
> CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt"
> 
> DO (insert into tblmasgl (KODEGL,NAMAREK) VALUES (new.KODEGL,
new.NAMAREK));
> 
>  
> 
> But I always get this error :
> 
> 
> ERROR:  column "kodegl" of relation "tblmasgl" does not exist
> 
> 
> Here is the Table Structure
> 
> CREATE TABLE "public"."tblmasgl" (
> 
>   "KODEGL" VARCHAR(15) NOT NULL,

Okay, you need to quote the column-name with " since they are in
uppercase.



DO (insert into tblmasgl ("KODEGL","NAMAREK") and maybe also
new."KODEGL" and new."NAMAREK".


Try it and tell if you have success.



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

am  Fri, dem 05.10.2007, um  7:16:06 +0800 mailte Hengky Lie folgendes:
> Yes, it works now ! Wow, the problem is in the field name. Changed it to
> lowercase solved the problem. Thank you to all ho give me this advice.
> 
> But now I have another question regarding to this field, what command I can
> use in UPDATE RULE to make these 2 fields (KODEGL and NAMAREK) keep syncron
> between these 2 tables (tblmasdbt and tblmasgl) ?

As Richard suggested, use TRIGGER instead RULE, but okay.




A little example, i hope, it helps:
(2 little tables t1 and t2 and a UPDATE-RULE on t1 with a 'do also')



test=# create table t1 (id int, val int);
CREATE TABLE
test=*# create table t2 (id int, val int);
CREATE TABLE
test=*# create rule r1 as on update to t1 do also update t2 set val =
new.val where id=new.id;
CREATE RULE
test=*# commit;
COMMIT
test=# insert into t1 values (1,1);
INSERT 0 1
test=*# insert into t1 values (2,2);
INSERT 0 1
test=*# insert into t2 values (1,1);
INSERT 0 1
test=*# insert into t2 values (2,2);
INSERT 0 1
test=*# update t1 set val =10 where id=1;
UPDATE 1
test=*# select * from t2;id | val
----+----- 2 |   2 1 |  10
(2 rows)


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net