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

Erik Jones <erik@myemma.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@myemma.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@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

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.