Re: Argument type list

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?

Thanks,
Gustavo.

PS: Please C.C. to me, I'm not subscribed in 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(at)myemma(dot)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

Erik Jones <erik(at)myemma(dot)com> writes:
> 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 sometype[]) RETURNS VOID

this is right ...

> execute 'select * from foo where foo_column::text in (' ||
> array_to_string(list, ',') || ');';

this is pretty horrid. Use = ANY(array) instead of trying to construct
an IN on the fly.

select * from foo where foo_column = any(list)

regards, tom lane

On Aug 23, 2007, at 1:27 PM, Tom Lane wrote:

> Erik Jones <erik(at)myemma(dot)com> writes:
>> 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 sometype[]) RETURNS VOID
>
> this is right ...
>
>> execute 'select * from foo where foo_column::text in (' ||
>> array_to_string(list, ',') || ');';
>
> this is pretty horrid. Use = ANY(array) instead of trying to
> construct
> an IN on the fly.
>
> select * from foo where foo_column = any(list)

Yes, I always forget about using ANY. Thx.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)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

Ok. It works well, but my argument type must be varchar (because java
conversions in my application). Then I want to convert varchar in
format "{int, int, ...}" to an integer array. Is there a function that
converts varchar -> integer [] ? I tried with casts and got no
success.

Tanks,
Gustavo.

On 8/23/07, Erik Jones <erik(at)myemma(dot)com> wrote:
> On Aug 23, 2007, at 1:27 PM, Tom Lane wrote:
>
> > Erik Jones <erik(at)myemma(dot)com> writes:
> >> 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 sometype[]) RETURNS VOID
> >
> > this is right ...
> >
> >> execute 'select * from foo where foo_column::text in (' ||
> >> array_to_string(list, ',') || ');';
> >
> > this is pretty horrid. Use = ANY(array) instead of trying to
> > construct
> > an IN on the fly.
> >
> > select * from foo where foo_column = any(list)
>
> Yes, I always forget about using ANY. Thx.
>
> Erik Jones
>
> Software Developer | Emma(r)
> erik(at)myemma(dot)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
>
>
>