Thread: 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@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. On 8/23/07, Erik Jones <erik@myemma.com> wrote: > 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(r) > 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 > > >