error when creating rule

Поиск
Список
Период
Сортировка
От Barbara Lindsey
Тема error when creating rule
Дата
Msg-id 2345.66.157.145.167.1068215140.squirrel@webmail.cog.ufl.edu
обсуждение исходный текст
Ответы Re: error when creating rule  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Re: error when creating rule  (Barbara Lindsey <blindsey@cog.ufl.edu>)
Список pgsql-general
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






В списке pgsql-general по дате отправления:

Предыдущее
От:
Дата:
Сообщение: Re: DDL for a single schema
Следующее
От: Thierry Missimilly
Дата:
Сообщение: Re: Changing the buffer size