Problem with type in rule

From: Sharon Cowling <sharon(dot)cowling(at)sslnz(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with type in rule
Date: 2001-11-18 21:17:00
Message-ID: 200111182113.fAILDrK07748@lambton.sslnz.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Wheeler 2001-11-18 21:21:30 Re: DBD::Pg BYTEA Character Escaping
Previous Message Tom Lane 2001-11-18 19:43:15 Re: usring CURSORS within functions