Thread: error when creating rule
Thank you for your help on the trigger question. The RULE worked for most
of the cases I had for this, but I have one that is giving me trouble.
Here are my table definitions:
CREATE SEQUENCE "stat_id_seq" cache 1;
CREATE TABLE "ref_status" (
"status_id" integer DEFAULT nextval('stat_id_seq') PRIMARY KEY,
"short_name" varchar(5),
"description" varchar(25),
"modified" timestamp with time zone DEFAULT current_timestamp,
"modified_by" varchar(50) DEFAULT current_user
);
CREATE SEQUENCE "prod_id_seq" cache 1;
CREATE TABLE "prod_data" (
"prod_id" integer DEFAULT nextval('prod_id_seq') PRIMARY KEY,
"client_id" integer NOT NULL,
"cat_id" integer NOT NULL,
"status_id" integer NOT NULL,
"modified" timestamp with time zone DEFAULT current_timestamp,
"modified_by" varchar(50) DEFAULT current_user,
CONSTRAINT prod_clnt_fk FOREIGN KEY (client_id)
REFERENCES ref_clients(client_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT prod_cat_fk FOREIGN KEY (cat_id)
REFERENCES ref_category(cat_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT prod_stat_fk FOREIGN KEY (status_id)
REFERENCES ref_status(status_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
TABLE job_data_bak looks just like job_data but with all constraints
removed.
Here is where the problem begins. When I try to create this rule:
CREATE RULE log_prod_upd AS ON UPDATE TO prod_data
where NEW.prod_id = OLD.prod_id
DO INSERT INTO job_data_bak VALUES (
OLD.prod_id,OLD.client_id,
OLD.cat_id, OLD.status_id,
OLD.modified,OLD.modified_by
);
This is the error I get:
ERROR: column "status_id" is of type 'integer' but expression is of type
'character varying'
You will need to rewrite or cast the expression
I tried casting status_id to text, but that doesn't work. I do not know
what I need to cast to make this work. Maybe another pair of eyes will
see something...
Here's the dump of the tables:
Table "prod_data"
Column | Type | Modifiers
--------------+--------------------------+----------------------------------------------------
prod_id | integer | not null default
nextval('prod_id_seq'::text)
client_id | integer | not null
cat_id | integer | not null
status_id | integer | not null
modified | timestamp with time zone | default
('now'::text)::timestamp(6) with time zone
modified_by | character varying(50) | default "current_user"()
Primary key: job_data_pkey
Table "ref_status"
Column | Type | Modifiers
--------------+--------------------------+----------------------------------------------------
status_id | integer | not null default
nextval('stat_id_seq'::text)
short_name | character varying(5) |
description | character varying(25) |
modified | timestamp with time zone | default
('now'::text)::timestamp(6) with time zone
modified_by | character varying(50) | default "current_user"()
Primary key: ref_status_pkey
Thank you,
Barb Lindsey
On Fri, 7 Nov 2003, Barbara Lindsey wrote:
> CREATE SEQUENCE "prod_id_seq" cache 1;
> CREATE TABLE "prod_data" (
> "prod_id" integer DEFAULT nextval('prod_id_seq') PRIMARY KEY,
> "client_id" integer NOT NULL,
> "cat_id" integer NOT NULL,
> "status_id" integer NOT NULL,
> "modified" timestamp with time zone DEFAULT current_timestamp,
> "modified_by" varchar(50) DEFAULT current_user,
> CONSTRAINT prod_clnt_fk FOREIGN KEY (client_id)
> REFERENCES ref_clients(client_id)
> ON DELETE CASCADE ON UPDATE CASCADE,
> CONSTRAINT prod_cat_fk FOREIGN KEY (cat_id)
> REFERENCES ref_category(cat_id)
> ON DELETE CASCADE ON UPDATE CASCADE,
> CONSTRAINT prod_stat_fk FOREIGN KEY (status_id)
> REFERENCES ref_status(status_id)
> ON DELETE CASCADE ON UPDATE CASCADE
> );
>
> TABLE job_data_bak looks just like job_data but with all constraints
> removed.
>
>
> Here is where the problem begins. When I try to create this rule:
>
> CREATE RULE log_prod_upd AS ON UPDATE TO prod_data
> where NEW.prod_id = OLD.prod_id
> DO INSERT INTO job_data_bak VALUES (
> OLD.prod_id,OLD.client_id,
> OLD.cat_id, OLD.status_id,
> OLD.modified,OLD.modified_by
> );
Was this intended to go to prod_data_bak as opposed to job_data_bak?
For what it's worth, this appears to be a case scenario problem within
postgres.(?!)
I removed the ref_status table entirely and all the places it is
referenced, and the problem switched to another variable on another rule
for another table...
Any suggestions? Workarounds?
> Thank you for your help on the trigger question. The RULE worked for
> most of the cases I had for this, but I have one that is giving me
> trouble. Here are my table definitions:
>
> CREATE SEQUENCE "stat_id_seq" cache 1;
> CREATE TABLE "ref_status" (
> "status_id" integer DEFAULT nextval('stat_id_seq') PRIMARY KEY,
> "short_name" varchar(5),
> "description" varchar(25),
> "modified" timestamp with time zone DEFAULT current_timestamp,
> "modified_by" varchar(50) DEFAULT current_user
> );
>
> CREATE SEQUENCE "prod_id_seq" cache 1;
> CREATE TABLE "prod_data" (
> "prod_id" integer DEFAULT nextval('prod_id_seq') PRIMARY KEY,
> "client_id" integer NOT NULL,
> "cat_id" integer NOT NULL,
> "status_id" integer NOT NULL,
> "modified" timestamp with time zone DEFAULT current_timestamp,
> "modified_by" varchar(50) DEFAULT current_user,
> CONSTRAINT prod_clnt_fk FOREIGN KEY (client_id)
> REFERENCES ref_clients(client_id)
> ON DELETE CASCADE ON UPDATE CASCADE,
> CONSTRAINT prod_cat_fk FOREIGN KEY (cat_id)
> REFERENCES ref_category(cat_id)
> ON DELETE CASCADE ON UPDATE CASCADE,
> CONSTRAINT prod_stat_fk FOREIGN KEY (status_id)
> REFERENCES ref_status(status_id)
> ON DELETE CASCADE ON UPDATE CASCADE
> );
>
> TABLE job_data_bak looks just like job_data but with all constraints
> removed.
>
>
> Here is where the problem begins. When I try to create this rule:
>
> CREATE RULE log_prod_upd AS ON UPDATE TO prod_data
> where NEW.prod_id = OLD.prod_id
> DO INSERT INTO job_data_bak VALUES (
> OLD.prod_id,OLD.client_id,
> OLD.cat_id, OLD.status_id,
> OLD.modified,OLD.modified_by
> );
>
>
> This is the error I get:
>
> ERROR: column "status_id" is of type 'integer' but expression is of
> type 'character varying'
> You will need to rewrite or cast the expression
>
>
>
> I tried casting status_id to text, but that doesn't work. I do not know
> what I need to cast to make this work. Maybe another pair of eyes will
> see something...
> Here's the dump of the tables:
> Table "prod_data"
> Column | Type | Modifiers
> --------------+--------------------------+----------------------------------------------------
> prod_id | integer | not null default
> nextval('prod_id_seq'::text)
> client_id | integer | not null
> cat_id | integer | not null
> status_id | integer | not null
> modified | timestamp with time zone | default
> ('now'::text)::timestamp(6) with time zone
> modified_by | character varying(50) | default "current_user"()
> Primary key: job_data_pkey
>
>
> Table "ref_status"
> Column | Type | Modifiers
> --------------+--------------------------+----------------------------------------------------
> status_id | integer | not null default
> nextval('stat_id_seq'::text)
> short_name | character varying(5) |
> description | character varying(25) |
> modified | timestamp with time zone | default
> ('now'::text)::timestamp(6) with time zone
> modified_by | character varying(50) | default "current_user"()
> Primary key: ref_status_pkey
>
>
>
> Thank you,
> Barb Lindsey
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
I see what you mean - yes - corrections to post below:
> On Fri, 7 Nov 2003, Barbara Lindsey wrote:
>
>> CREATE SEQUENCE "prod_id_seq" cache 1;
>> CREATE TABLE "prod_data" (
>> "prod_id" integer DEFAULT nextval('prod_id_seq') PRIMARY KEY,
>> "client_id" integer NOT NULL,
>> "cat_id" integer NOT NULL,
>> "status_id" integer NOT NULL,
>> "modified" timestamp with time zone DEFAULT
>> current_timestamp, "modified_by" varchar(50) DEFAULT
>> current_user,
>> CONSTRAINT prod_clnt_fk FOREIGN KEY (client_id)
>> REFERENCES ref_clients(client_id)
>> ON DELETE CASCADE ON UPDATE CASCADE,
>> CONSTRAINT prod_cat_fk FOREIGN KEY (cat_id)
>> REFERENCES ref_category(cat_id)
>> ON DELETE CASCADE ON UPDATE CASCADE,
>> CONSTRAINT prod_stat_fk FOREIGN KEY (status_id)
>> REFERENCES ref_status(status_id)
>> ON DELETE CASCADE ON UPDATE CASCADE
>> );
>>
>> TABLE prod_data_bak looks just like prod_data but with all constraints
>> removed.
>>
>>
>> Here is where the problem begins. When I try to create this rule:
>>
>> CREATE RULE log_prod_upd AS ON UPDATE TO prod_data
>> where NEW.prod_id = OLD.prod_id
>> DO INSERT INTO prod_data_bak VALUES (
>> OLD.prod_id,OLD.client_id,
>> OLD.cat_id, OLD.status_id,
>> OLD.modified,OLD.modified_by
>> );
>
> Was this intended to go to prod_data_bak as opposed to job_data_bak?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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
On Fri, 7 Nov 2003, Barbara Lindsey wrote:
> I see what you mean - yes - corrections to post below:
>
> > On Fri, 7 Nov 2003, Barbara Lindsey wrote:
> >
> >> CREATE SEQUENCE "prod_id_seq" cache 1;
> >> CREATE TABLE "prod_data" (
> >> "prod_id" integer DEFAULT nextval('prod_id_seq') PRIMARY KEY,
> >> "client_id" integer NOT NULL,
> >> "cat_id" integer NOT NULL,
> >> "status_id" integer NOT NULL,
> >> "modified" timestamp with time zone DEFAULT
> >> current_timestamp, "modified_by" varchar(50) DEFAULT
> >> current_user,
> >> CONSTRAINT prod_clnt_fk FOREIGN KEY (client_id)
> >> REFERENCES ref_clients(client_id)
> >> ON DELETE CASCADE ON UPDATE CASCADE,
> >> CONSTRAINT prod_cat_fk FOREIGN KEY (cat_id)
> >> REFERENCES ref_category(cat_id)
> >> ON DELETE CASCADE ON UPDATE CASCADE,
> >> CONSTRAINT prod_stat_fk FOREIGN KEY (status_id)
> >> REFERENCES ref_status(status_id)
> >> ON DELETE CASCADE ON UPDATE CASCADE
> >> );
> >>
> >> TABLE prod_data_bak looks just like prod_data but with all constraints
> >> removed.
> >>
> >>
> >> Here is where the problem begins. When I try to create this rule:
> >>
> >> CREATE RULE log_prod_upd AS ON UPDATE TO prod_data
> >> where NEW.prod_id = OLD.prod_id
> >> DO INSERT INTO prod_data_bak VALUES (
> >> OLD.prod_id,OLD.client_id,
> >> OLD.cat_id, OLD.status_id,
> >> OLD.modified,OLD.modified_by
> >> );
> >
> > Was this intended to go to prod_data_bak as opposed to job_data_bak?
What version are you running?
I had to drop the extra references constraints to other tables that
weren't given, but had no problems with the creation on the rule once I
did so, given creating a prod_data_bak that didn't have any of the
references constraints (or the primary key) and it seemed to work for me
(using my 7.3.4 system)
Running with version 7.2.1
>
> On Fri, 7 Nov 2003, Barbara Lindsey wrote:
>
>> I see what you mean - yes - corrections to post below:
>>
>> > On Fri, 7 Nov 2003, Barbara Lindsey wrote:
>> >
>> >> CREATE SEQUENCE "prod_id_seq" cache 1;
>> >> CREATE TABLE "prod_data" (
>> >> "prod_id" integer DEFAULT nextval('prod_id_seq') PRIMARY
>> KEY,
>> >> "client_id" integer NOT NULL,
>> >> "cat_id" integer NOT NULL,
>> >> "status_id" integer NOT NULL,
>> >> "modified" timestamp with time zone DEFAULT
>> >> current_timestamp, "modified_by" varchar(50) DEFAULT
>> >> current_user,
>> >> CONSTRAINT prod_clnt_fk FOREIGN KEY (client_id)
>> >> REFERENCES ref_clients(client_id)
>> >> ON DELETE CASCADE ON UPDATE CASCADE,
>> >> CONSTRAINT prod_cat_fk FOREIGN KEY (cat_id)
>> >> REFERENCES ref_category(cat_id)
>> >> ON DELETE CASCADE ON UPDATE CASCADE,
>> >> CONSTRAINT prod_stat_fk FOREIGN KEY (status_id)
>> >> REFERENCES ref_status(status_id)
>> >> ON DELETE CASCADE ON UPDATE CASCADE
>> >> );
>> >>
>> >> TABLE prod_data_bak looks just like prod_data but with all
>> constraints removed.
>> >>
>> >>
>> >> Here is where the problem begins. When I try to create this rule:
>> >>
>> >> CREATE RULE log_prod_upd AS ON UPDATE TO prod_data
>> >> where NEW.prod_id = OLD.prod_id
>> >> DO INSERT INTO prod_data_bak VALUES (
>> >> OLD.prod_id,OLD.client_id,
>> >> OLD.cat_id, OLD.status_id,
>> >> OLD.modified,OLD.modified_by
>> >> );
>> >
>> > Was this intended to go to prod_data_bak as opposed to job_data_bak?
>
> What version are you running?
>
> I had to drop the extra references constraints to other tables that
> weren't given, but had no problems with the creation on the rule once I
> did so, given creating a prod_data_bak that didn't have any of the
> references constraints (or the primary key) and it seemed to work for me
> (using my 7.3.4 system)