Re: Argument type list

On Aug 23, 2007, at 11:56 AM, Gustavo Tonini wrote:

> I want to create a function that receive a list argument and filter
> data with IN operator. Example:
>
> CREATE OR REPLACE FUNCTION "public"."ffoo" (list ???) RETURNS VOID AS
> $body$
> BEGIN
>   select * from foo where foo_column in list;
> END;
> $body$
> LANGUAGE 'plpgsql' ;
>
> I played with arrays but I got no success...
> Is it possible? How proceed?

Without knowing the data type of foo_column we can't really give a
"best" solution, but with an array you could do something like (not
tested):

CREATE OR REPLACE FUNCTION public.ffoo(list sometype[]) RETURNS VOID
AS $$
BEGIN
  execute 'select * from foo where foo_column::text in (' ||
array_to_string(list, ',') || ');';
END;
$$
LANGUAGE plpgsql;

Note that if foo_column is already a text type you don't need the cast.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com