Thread: Possible pl/pgsql bug

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

Peter Darley wrote:
> 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

    Indeed, it does.

    While  it  sometimes  does make sense to use one and the same
    function for multiple triggers, I don't see it in this  case.

    And IMHO it's a bit bogus anyway if attributes with different
    meaning and datatype in two tables have the same name.  Could
    be irritating at least.

    I suggest writing separate trigger functions.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Jan,
    I find it very useful to have tables that are basically identical except
for one field that holds a different type of data.  It allows me to automate
a lot of stuff in my application and not have to tell it weather it is going
to get back Boolean, text, date, interval etc. data.  Similarly it would be
nice to have as few objects in the db as possible, just so it's easier to
understand and manage.

    Anyway, I take it that this is not a bug and I'll create separate trigger
functions for each of my tables.  :)

    Thanks for getting back to me so quickly!

Peter Darley

-----Original Message-----
From: Jan Wieck [mailto:janwieck@yahoo.com]
Sent: Monday, April 22, 2002 1:23 PM
To: Peter Darley
Cc: Pgsql-General
Subject: Re: [GENERAL] Possible pl/pgsql bug


Peter Darley wrote:
> 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

    Indeed, it does.

    While  it  sometimes  does make sense to use one and the same
    function for multiple triggers, I don't see it in this  case.

    And IMHO it's a bit bogus anyway if attributes with different
    meaning and datatype in two tables have the same name.  Could
    be irritating at least.

    I suggest writing separate trigger functions.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #