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_lovsDETAIL: 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!
http://games.msn.com/en/flexicon/default.htm?icid=flexicon_ wlmemailtaglinemarch07