Friends,
I don't know if this is a bug or not, but it does seem a bit weird.
When I have a pl/pgsql trigger function it seems to cache the data types of
New.field and if the data type is different next time the function is called
(because it's being called from a different table) it gives a 'type of
<something> doesn't match that when preparing the plan' error.
In my case the error I'm getting is: type of new.indicator doesn't match
that when preparing the plan
I only get this error when I insert something into my answers_bool table
and my answers_text table in the same transaction. Both tables and the
function are shown below.
I've fixed my problem by changing answers_bool.indicator to text from a
varchar(255).
Thanks,
Peter Darley
My function is very simple:
CREATE or replace FUNCTION "insert_time"() RETURNS "opaque" AS '
BEGIN
DELETE FROM answers_deleted WHERE SampleID=NEW.sampleid AND
Indicator=NEW.indicator;
RETURN NULL;
END;
' LANGUAGE 'plpgsql';
My Tables:
Table "answers_bool"
Column | Type | Modifiers
-----------+--------------------------+-------------------------------------
--------------
id | integer | not null default
nextval('all_answers_seq'::text)
sampleid | integer | not null
value | boolean |
indicator | character varying(255) | not null
surveyid | integer | not null
time | timestamp with time zone | not null default now()
Primary key: answers_bool_newer_pkey
Unique keys: a_b_surveyid_indicator_newer
Triggers: a_b_delete_new,
a_b_insert_new,
a_b_update_new
Table "answers_text"
Column | Type |
Modifiers
----------------+-----------------------------+-----------------------------
----------------------
id | integer | not null default
nextval('all_answers_seq'::text)
sampleid | integer | not null
value | text |
indicator | text | not null
surveyid | integer | not null
origionalvalue | text |
coded | boolean | default 'f'::bool
time | timestamp(0) with time zone | not null default now()
Indexes: a_t_indicator,
a_t_sampleid,
a_t_surveyid
Primary key: answers_text_new_pkey
Unique keys: a_t_sampleid_indicator
Triggers: a_t_delete,
a_t_insert,
a_t_update
