pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAnother question in functions
2+ messages / 2 participants
[nested] [flat]
* Another question in functions
@ 2007-04-09 01:59 Karthikeyan Sundaram <skarthi98@hotmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Karthikeyan Sundaram @ 2007-04-09 01:59 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; pgsql-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=
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Another question in functions
@ 2007-04-09 14:24 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Karthikeyan Sundaram <skarthi98@hotmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2007-04-09 14:24 UTC (permalink / raw)
To: Karthikeyan Sundaram <skarthi98@hotmail.com>; +Cc: pgsql-admin@postgresql.org; pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-04-09 14:24 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-09 01:59 Another question in functions Karthikeyan Sundaram <skarthi98@hotmail.com>
2007-04-09 14:24 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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