Thread: Rule Error

Rule Error

From

"Hengky Lie"

Date:

04 October 2007, 11:23:19

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Dear Friends, </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have problem with rule and tried several times to solve it but not yet success. Hope someone can
helpme.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have 2 tables : tblmasdbt and tblmasgl. </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I want on every record insertion in tblmasdbt, that record also automatically insert into tblmasdbt.
Ineed only 2 related field.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">So I create rule like this</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">--------------- SQL ---------------</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt" </span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">DO (insert into tblmasgl (KODEGL,NAMAREK) VALUES (new.KODEGL, new.NAMAREK));</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">But I always get this error :</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">---------- ERROR MESSAGE ----------</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">ERROR:  column "kodegl" of relation "tblmasgl" does not exist</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Here is the Table Structure </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">---------------------------</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TABLE "public"."tblmasgl" (</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  "KODEGL" VARCHAR(15) NOT NULL, </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  "NAMAREK" VARCHAR(50), </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  "GOLONGAN" VARCHAR(10), </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  "AWAL" DOUBLE PRECISION DEFAULT 0, </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  "Operator" VARCHAR(3), </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  CONSTRAINT "tblmasgl_pkey" PRIMARY KEY("KODEGL"), </span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">  CONSTRAINT "tblmasgl_fk" FOREIGN KEY ("KODEGL")</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">    REFERENCES "public"."tbltragl"("KODEGL")</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">    ON DELETE CASCADE</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">    ON UPDATE NO ACTION</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">    NOT DEFERRABLE</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">) WITHOUT OIDS;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TABLE "public"."tblmasdbt" (</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  "KODEGL" VARCHAR(15) NOT NULL, </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  "NAMAREK" VARCHAR(50), </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  "ALAMAT" VARCHAR(75), </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  "Telp" VARCHAR(50), </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  "Facs" VARCHAR(50), </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  "KOTA" VARCHAR(30), </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  "HP" VARCHAR(20), </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  "Plafond" DOUBLE PRECISION DEFAULT 0, </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  "Operator" VARCHAR(3), </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  "SALDOAWAL" DOUBLE PRECISION DEFAULT 0, </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  CONSTRAINT "tblmasdbt_pkey" PRIMARY KEY("KODEGL")</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">) WITHOUT OIDS;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hope someone could help me. Thanks a lot</span></font></div>

Re: Rule Error

From

Richard Huxton

Date:

04 October 2007, 12:53:40

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

Re: Rule Error

From

"Bart Degryse"

Date:

04 October 2007, 12:57:45

st1\:*{behavior:url(#default#ieooui) } You have defined the fields KODEGL and NAMAREK as uppercased field names.

In your rule you refer to an unquoted field KODEGL twice and twice to an unquoted field NAMAREK.

Default behaviour of PostgreSQL for unquoted fieldnames is to lowercase them.

As such these fields effectively don't exist in your tables.

Try

CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt"

DO (insert into tblmasgl ("KODEGL","NAMAREK") VALUES (new."KODEGL", new."NAMAREK"));
>>> "Hengky Lie" <hengkyliwandouw@gmail.com> 2007-10-04 13:22 >>>

Dear Friends,

I have problem with rule and tried several times to solve it but not yet success. Hope someone can help me.

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.

So I create rule like this

--------------- SQL ---------------

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 MESSAGE ----------

ERROR: column "kodegl" of relation "tblmasgl" does not exist

Here is the Table Structure

---------------------------

CREATE TABLE "public"."tblmasgl" (

"KODEGL" VARCHAR(15) NOT NULL,

"NAMAREK" VARCHAR(50),

"GOLONGAN" VARCHAR(10),

"AWAL" DOUBLE PRECISION DEFAULT 0,

"Operator" VARCHAR(3),

CONSTRAINT "tblmasgl_pkey" PRIMARY KEY("KODEGL"),

CONSTRAINT "tblmasgl_fk" FOREIGN KEY ("KODEGL")

REFERENCES "public"."tbltragl"("KODEGL")

ON DELETE CASCADE

ON UPDATE NO ACTION

NOT DEFERRABLE

) WITHOUT OIDS;

CREATE TABLE "public"."tblmasdbt" (

"KODEGL" VARCHAR(15) NOT NULL,

"NAMAREK" VARCHAR(50),

"ALAMAT" VARCHAR(75),

"Telp" VARCHAR(50),

"Facs" VARCHAR(50),

"KOTA" VARCHAR(30),

"HP" VARCHAR(20),

"Plafond" DOUBLE PRECISION DEFAULT 0,

"Operator" VARCHAR(3),

"SALDOAWAL" DOUBLE PRECISION DEFAULT 0,

CONSTRAINT "tblmasdbt_pkey" PRIMARY KEY("KODEGL")

) WITHOUT OIDS;

Hope someone could help me. Thanks a lot

Re: Rule Error

From

"A. Kretschmer"

Date:

04 October 2007, 13:01:00

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

Re: Rule Error

From

"Hengky Lie"

Date:

05 October 2007, 00:00:35

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

Re: Rule Error

From

"A. Kretschmer"

Date:

05 October 2007, 04:56:46

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