Possible pl/pgsql bug

Поиск
Список
Период
Сортировка
От Peter Darley
Тема Possible pl/pgsql bug
Дата
Msg-id NNEAICKPNOGDBHNCEDCPIEMFCHAA.pdarley@kinesis-cem.com
обсуждение исходный текст
Ответы Re: Possible pl/pgsql bug  (Jan Wieck <janwieck@yahoo.com>)
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Francisco Reyes
Дата:
Сообщение: Which OS better for postgresql? FreeBSD or Linux?
Следующее
От: Peter Eisentraut
Дата:
Сообщение: Re: very concerning, tables hopped from one database to