Thread: Problem with type in rule

Hi

I have rule for a view for inserting into one table and updating another.
Problem: I get the following error when I create the rule:

ERROR:  Attribute 'permit_printed' is of type 'int4' but expression is of type 'varchar'
        You will need to rewrite or cast the expression

I don't understand this as I am inserting 1 into the database, which is an int!

View:
DROP VIEW fpermit_insert_vu;
CREATE VIEW fpermit_insert_vu
AS SELECT
t.permit_id,t.person_id,t.date_from,t.date_to,t.location,t.purpose,t.subpurpose,t.vehicle_rego,t.vehicle_type,t.dogs,t.permit_conditions,t.other_info,t.issued_by,t.issue_date,t.firearms_licence,t.drivers_licence,t.permit_printed,k.key_code,k.date_key_issued,k.date_key_due,k.key_issued_by
FROM ltft_permit t LEFT OUTER JOIN ltft_key k ON t.permit_id = k.permit_id;

Rule:
CREATE RULE fpermit_insert_ru AS ON INSERT TO fpermit_insert_vu
DO INSTEAD(
INSERT into ltft_permit

VALUES(new.permit_id,new.person_id,new.date_from,new.date_to,new.location,new.purpose,new.subpurpose,new.vehicle_rego,new.vehicle_type,new.dogs,new.permit_conditions,new.other_info,new.issued_by,new.issue_date,new.firearms_licence,new.drivers_licence,1);
UPDATE ltft_key
SET date_key_issued =
new.date_key_issued,date_key_due=new.date_key_due,key_issued_by=new.key_issued_by,permit_id=new.permit_id
WHERE key_code = new.key_code;
);

Table:
user=> \d ltft_permit
                  Table "ltft_permit"
     Attribute     |          Type          | Modifier
-------------------+------------------------+----------
 permit_id         | integer                | not null
 person_id         | integer                | not null
 date_from         | date                   | not null
 date_to           | date                   | not null
 location          | character varying(30)  | not null
 purpose           | character varying(30)  | not null
 subpurpose        | character varying(30)  | not null
 vehicle_rego      | character varying(6)   |
 vehicle_type      | character varying(30)  |
 dogs              | character varying(3)   |
 permit_conditions | character varying(200) |
 other_info        | character varying(100) |
 issued_by         | character varying(12)  | not null
 issue_date        | date                   |
 permit_printed    | integer                |
 firearms_licence  | character varying(20)  |
 drivers_licence   | character varying(10)  |
Index: ltft_permit_pkey


Any ideas or suggestions would be greatly appreciated.

Regards,

Sharon Cowling

On Sun, 18 Nov 2001, Sharon Cowling wrote:

> Hi
>
> I have rule for a view for inserting into one table and updating another.
> Problem: I get the following error when I create the rule:
>
> ERROR:  Attribute 'permit_printed' is of type 'int4' but expression is of type 'varchar'
>         You will need to rewrite or cast the expression
>
> I don't understand this as I am inserting 1 into the database, which is an int!
>
> INSERT into ltft_permit
> VALUES(new.permit_id,new.person_id,new.date_from,new.date_to,new.location,
> new.purpose,new.subpurpose,new.vehicle_rego,new.vehicle_type,new.dogs,
> new.permit_conditions,new.other_info,new.issued_by,new.issue_date,
> new.firearms_licence,new.drivers_licence,1);

As a suggestion, list the columns on your insert. That way it's much
easier to figure out what data goes with what column:

Looking at the columns below and the data above it looks like you put
the 1 in the wrong place.  The last 3 columns should be permit_printed,
firearms_licence, drivers_license, where above you're using
firearms_licence, drivers_license, permit_printed.  The ordering
is the table's ordering unless you specify column names.

> Table:
> user=> \d ltft_permit
>                   Table "ltft_permit"
>      Attribute     |          Type          | Modifier
> -------------------+------------------------+----------
>  permit_id         | integer                | not null
>  person_id         | integer                | not null
>  date_from         | date                   | not null
>  date_to           | date                   | not null
>  location          | character varying(30)  | not null
>  purpose           | character varying(30)  | not null
>  subpurpose        | character varying(30)  | not null
>  vehicle_rego      | character varying(6)   |
>  vehicle_type      | character varying(30)  |
>  dogs              | character varying(3)   |
>  permit_conditions | character varying(200) |
>  other_info        | character varying(100) |
>  issued_by         | character varying(12)  | not null
>  issue_date        | date                   |
>  permit_printed    | integer                |
>  firearms_licence  | character varying(20)  |
>  drivers_licence   | character varying(10)  |
> Index: ltft_permit_pkey