Skip site navigation (1) Skip section navigation (2)

Another question in functions

From: Karthikeyan Sundaram <skarthi98(at)hotmail(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>, <pgsql-sql(at)postgresql(dot)org>
Subject: Another question in functions
Date: 2007-04-09 01:59:13
Message-ID: BAY131-W128D92C375C2D708339994B0590@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-sql
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

Responses

pgsql-admin by date

Next:From: s khDate: 2007-04-09 09:38:18
Subject: Ditributed PosgreSQL
Previous:From: Tom LaneDate: 2007-04-08 23:20:46
Subject: Re: Question on pgpsql function

pgsql-sql by date

Next:From: sql4-en.narod.ruDate: 2007-04-09 06:42:24
Subject: new idea
Previous:From: Michael FuhrDate: 2007-04-09 01:07:27
Subject: Re: MD5 sums of large objects

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group