error when creating rule

[Barbara Lindsey]

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

[Stephan Szabo]

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?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Barbara Lindsey]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Barbara Lindsey]

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Stephan Szabo]

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)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Barbara Lindsey]

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)

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

http://www.postgresql.org/docs/faqs/FAQ.html