Possible pl/pgsql bug

From: "Peter Darley" <pdarley(at)kinesis-cem(dot)com>
To: "Pgsql-General" <pgsql-general(at)postgresql(dot)org>
Subject: Possible pl/pgsql bug
Date: 2002-04-23 07:44:20
Message-ID: NNEAICKPNOGDBHNCEDCPIEMFCHAA.pdarley@kinesis-cem.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message martin tan 2002-04-23 08:05:30 help!
Previous Message josecosta 2002-04-23 06:55:20 delphi