Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 8D0399FA617; Sun, 8 Apr 2007 22:59:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 09991-07; Sun, 8 Apr 2007 22:59:14 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from bay0-omc3-s5.bay0.hotmail.com (bay0-omc3-s5.bay0.hotmail.com [65.54.246.205]) by postgresql.org (Postfix) with ESMTP id 7AF209FA468; Sun, 8 Apr 2007 22:59:14 -0300 (ADT) Received: from BAY131-W12 ([65.55.136.47]) by bay0-omc3-s5.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2668); Sun, 8 Apr 2007 18:59:13 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_b8c37b52-4ac9-4a06-8d53-8a331c60a28e_" X-Originating-IP: [67.116.31.14] From: Karthikeyan Sundaram To: , Subject: Another question in functions Date: Sun, 8 Apr 2007 18:59:13 -0700 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 09 Apr 2007 01:59:13.0168 (UTC) FILETIME=[ABCA5100:01C77A4A] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/74 X-Sequence-Number: 25138 --_b8c37b52-4ac9-4a06-8d53-8a331c60a28e_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi team, =20 I have a requirement like this. =20 create table valid_lovs (code_id int not null,lov_value int not null ,desc= ription varchar(256),status bit(1) not null default '1',constraint lov_pk p= rimary key (code_id,lov_value)); insert into valid_lovs (code_id,lov_value,description) values (1,1,'1000 do= wnloads');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,descrip= tion) values (10,2,'Singapore dollar');insert into valid_lovs (code_id,lov_= value,description) values (20,1,'Audio');insert into valid_lovs (code_id,lo= v_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,'IMP= RESSION_LOV');insert into valid_lovs (code_id, lov_value,description) value= s (1000,10,'CURRENCY_LOV');insert into valid_lovs (code_id, lov_value,descr= iption) values (1000,20,'MEDIA_FORMAT'); =20 I need to write 2 functions. =20 1) Find_LOV. In this function I will pass only a text message but should re= turn an array. =20 create or replace function find_lov_func(in p_1 anyelement, out p_2 anyarra= y) as$$ select array[x.code_id, x.lov_value] from valid_lovs x, valid_lov= s y where y.description =3D $1 and x.code_id =3D y.lov_value;$$language= sql; =20 select find_lov_func('CURRENCY_LOV'::text); =20 I should get an output of=20 {10,1} {10,2} instead I am getting =20 ERROR: return type mismatch in function declared to return text[]DETAIL: = Actual return type is integer[].CONTEXT: SQL function "find_lov_func" duri= ng startup =20 Q) How will I resolve this. I need to get array of integer only. =20 2) get_lov function: In this function, I will pass a text field and I shou= ld get an integer and the text as output for example =20 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_lov= s y where y.description =3D $1 and x.code_id =3D y.lov_value;$$language= sql; =20 =20 ERROR: return type mismatch in function declared to return valid_lovsDETAI= L: Final SELECT returns character varying instead of integer at column 2.C= ONTEXT: SQL function "get_lov_func" =20 Can somebody help me in this? =20 Regards skarthi =20 =20 _________________________________________________________________ Take a break and play crossword puzzles - FREE! http://games.msn.com/en/flexicon/default.htm?icid=3Dflexicon_ wlmemailtagli= nemarch07= --_b8c37b52-4ac9-4a06-8d53-8a331c60a28e_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi team,
 
    I have a requirement like this.
 
create table valid_lovs (
code_id int not null,
lov_value  int n= ot 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,'100= 0 downloads');
insert into valid_lovs (code_id,lov_value,description) va= lues (1,2,'2000 downloads');
insert into valid_lovs (code_id,lov_value,d= escription) values (10,1,'US Dollar');
insert into valid_lovs (code_id,l= ov_value,description) values (10,2,'Singapore dollar');
insert into vali= d_lovs (code_id,lov_value,description) values (20,1,'Audio');
insert int= o valid_lovs (code_id,lov_value,description) values (20,2,'Video');
inse= rt into valid_lovs (code_id,lov_value,description) values (20,3,'Overlay');=
insert into valid_lovs (code_id, lov_value,description) values (1000,1,'IMP= RESSION_LOV');
insert into valid_lovs (code_id, lov_value,description) v= alues (1000,10,'CURRENCY_LOV');
insert into valid_lovs (code_id, lov_val= ue,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 re= turn an array.
 
create or replace function find_lov_func(in p_1 anyelement, out p_2 anyarra= y) as
$$
   select array[x.code_id, x.lov_value] from valid= _lovs x, valid_lovs y
   where y.description =3D $1
 &= nbsp; and x.code_id =3D 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 functi= on "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 =3D $1
 &= nbsp; and x.code_id =3D 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! = --_b8c37b52-4ac9-4a06-8d53-8a331c60a28e_--