Thread: Another question in functions

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!

On Sun, 8 Apr 2007, Karthikeyan Sundaram wrote:

>
> 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));

> 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;

Well, I don't think the above does what you want for a couple reasons.
First, anyelement/anyarray don't do what you want, they make it so that
the output array is of the type that came in from the anyelement. Second,
your example shows you getting multiple arrays back AFAICT, which the
above also wouldn't do I believe. Perhaps find_lov_func(in p_1 text)
returns setof int[] might get closer to your intent.

> 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;

In this case you say you're returning a valid_lovs, but you're not, you're
only returning some of the fields. Either get all the columns from
valid_lovs, make a new type to represent what you're returning or make it
return setof record and specify the field information at call time.