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

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

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)