Re: error when creating rule - Mailing list pgsql-general

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