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

Re: Another question in functions

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Karthikeyan Sundaram <skarthi98(at)hotmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: Another question in functions
Date: 2007-04-09 14:24:39
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-adminpgsql-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.

In response to

pgsql-admin by date

Next:From: Tom LaneDate: 2007-04-09 14:53:21
Subject: Re: xlog related error
Previous:From: PankajDate: 2007-04-09 12:51:11
Subject: Installing Postgresql on Windows

pgsql-sql by date

Next:From: Dirk JagdmannDate: 2007-04-09 15:11:37
Subject: Re: MD5 sums of large objects
Previous:From: Andrew SullivanDate: 2007-04-09 14:14:46
Subject: Re: new idea

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