pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing CASE in plpgsql causes 'ERROR: cache lookup failed'
2+ messages / 2 participants
[nested] [flat]
* Using CASE in plpgsql causes 'ERROR: cache lookup failed'
@ 2010-04-14 09:48 Mario Splivalo <mario.splivalo@megafon.hr>
2010-04-14 23:11 ` Re: Using CASE in plpgsql causes 'ERROR: cache lookup failed' Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Mario Splivalo @ 2010-04-14 09:48 UTC (permalink / raw)
To: 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 character varying 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$
DECLARE
serviceType type_enum_service_type;
impressionsLeft integer;
messageId integer;
userId integer;
BEGIN
CASE 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Using CASE in plpgsql causes 'ERROR: cache lookup failed'
2010-04-14 09:48 Using CASE in plpgsql causes 'ERROR: cache lookup failed' Mario Splivalo <mario.splivalo@megafon.hr>
@ 2010-04-14 23:11 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2010-04-14 23:11 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; +Cc: pgsql-sql
Mario Splivalo <mario.splivalo@megafon.hr> writes:
> When I call 'service_something' function and provide nonexistent
> service_id I get this error:
> ERROR: cache lookup failed for type 37
Hmm. Looks like exec_eval_expr should be more careful about setting
a result datatype when the expression returns no rows. NULL is NULL,
but it still has a defined datatype ...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-04-14 23:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-14 09:48 Using CASE in plpgsql causes 'ERROR: cache lookup failed' Mario Splivalo <mario.splivalo@megafon.hr>
2010-04-14 23:11 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox