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'
|
Список | 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 по дате отправления: