Hi team,
I have a requirement like this.
create table valid_lovs (
code_id int not null,
lov_value int not null ,
description varchar(256),
status bit(1) not null default '1',
constraint lov_pk primary key (code_id,lov_value));
insert into valid_lovs (code_id,lov_value,description) values (1,1,'1000 downloads');
insert into valid_lovs (code_id,lov_value,description) values (1,2,'2000 downloads');
insert into valid_lovs (code_id,lov_value,description) values (10,1,'US Dollar');
insert into valid_lovs (code_id,lov_value,description) values (10,2,'Singapore dollar');
insert into valid_lovs (code_id,lov_value,description) values (20,1,'Audio');
insert into valid_lovs (code_id,lov_value,description) values (20,2,'Video');
insert into valid_lovs (code_id,lov_value,description) values (20,3,'Overlay');
insert into valid_lovs (code_id, lov_value,description) values (1000,1,'IMPRESSION_LOV');
insert into valid_lovs (code_id, lov_value,description) values (1000,10,'CURRENCY_LOV');
insert into valid_lovs (code_id, lov_value,description) values (1000,20,'MEDIA_FORMAT');
I need to write 2 functions.
1) Find_LOV. In this function I will pass only a text message but should return an array.
create or replace function find_lov_func(in p_1 anyelement, out p_2 anyarray) as
$$
select array[x.code_id, x.lov_value] from valid_lovs x, valid_lovs y
where y.description = $1
and x.code_id = y.lov_value;
$$
language sql;
select find_lov_func('CURRENCY_LOV'::text);
I should get an output of
{10,1}
{10,2}
instead I am getting
ERROR: return type mismatch in function declared to return text[]
DETAIL: Actual return type is integer[].
CONTEXT: SQL function "find_lov_func" during startup
Q) How will I resolve this. I need to get array of integer only.
2) get_lov function: In this function, I will pass a text field and I should get an integer and the text as output
for example
create or replace function get_lov_func(in p_1 varchar) returns setof valid_lovs as
$$
select x.lov_value, x.description from valid_lovs x, valid_lovs y
where y.description = $1
and x.code_id = y.lov_value;
$$
language sql;
ERROR: return type mismatch in function declared to return valid_lovs
DETAIL: Final SELECT returns character varying instead of integer at column 2.
CONTEXT: SQL function "get_lov_func"
Can somebody help me in this?
Regards
skarthi
Take a break and play crossword puzzles - FREE!
Play Now!
