Another question in functions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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!

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux