Using CASE in plpgsql causes 'ERROR: cache lookup failed'

Поиск
Список
Период
Сортировка
От Mario Splivalo
Тема Using CASE in plpgsql causes 'ERROR: cache lookup failed'
Дата
Msg-id 4BC58F7B.4000405@megafon.hr
обсуждение исходный текст
Ответы Re: Using CASE in plpgsql causes 'ERROR: cache lookup failed'  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-sql
I have an enum-type, like this:

CREATE TYPE type_enum_service_type AS ENUM  ('Banner', 'Ticker', 'Memo');

Then I have a table, like this:

CREATE TABLE services ( service_id integer NOT NULL, service_type type_enum_service_type NOT NULL, service_keyword
charactervarying NOT NULL, service_time_created timestamp with time zone NOT NULL DEFAULT now(),
 
);

And, I have a plpgsql function like this:

CREATE OR REPLACE FUNCTION service_something(a_service_id integer) RETURNS void AS
$BODY$
DECLAREserviceType type_enum_service_type;impressionsLeft integer;messageId integer;userId integer;

BEGINCASE service_type FROM services WHERE service_id = a_service_id    WHEN 'Banner' THEN        RAISE NOTICE 'It is
Banner!';   WHEN 'Ticker' THEN        RAISE NOTICE 'It is Ticker!';    WHEN 'Memo' THEN        RAISE NOTICE 'It is
Memo!';   ELSE        RAISE EXCEPTION 'It is strange!';END CASE;
 
RETURN;

END
$BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER COST 100


Then I insert some data:

INSERT INTO services (1, 'Banner', 'kw-banner', now());
INSERT INTO services (2, 'Banner', 'kw-banner', now());
INSERT INTO services (2, 'Banner', 'kw-banner', now());


When I call 'service_something' function and provide nonexistent
service_id I get this error:

ERROR:  cache lookup failed for type 37

When I repeat the query (SELECT service_something(1);) the error is like
this:
ERROR:  cache lookup failed for type 0

Is this desired behavior so that first I need to check if service_id is
existent, or is this a bug? :)
Mike

P.S. PostgreSQL 8.4.2 on x86_64-unknown-linux-gnu, compiled by GCC
gcc-4.3.real (Ubuntu 4.3.3-5ubuntu4) 4.3.3, 64-bit



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

Предыдущее
От: Rob Sargent
Дата:
Сообщение: Re: [ADMIN] Getting the initdb parameter values
Следующее
От: Louis-David Mitterrand
Дата:
Сообщение: graphing time series data