From: Karthikeyan Sundaram <skarthi98@hotmail.com>
To: pgsql-admin@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Another question in functions
Date: Sun, 8 Apr 2007 18:59:13 -0700
Message-ID: <BAY131-W128D92C375C2D708339994B0590@phx.gbl> (raw)
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=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: skarthi98@hotmail.com, pgsql-admin@postgresql.org
Subject: Re: Another question in functions
In-Reply-To: <BAY131-W128D92C375C2D708339994B0590@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox