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.
