How to write a function using arrays as input parameter in postgres 8.2

Question

I am fairly new to Postgres and would like help with writing function that has an array as input parameter. I am using the function for a SSRS report and would like to add multiselect functionality

CREATE OR REPLACE FUNCTION foo(facid bigint, dptname text[])
RETURNS SETOF tmpdb AS
$BODY$
select * from tblitem
where dptname = $2 and facid = $1
$BODY$
LANGUAGE sql VOLATILE;

In the dptname you can choose either dress or shoes but would like to display result set of choosing both. VARIADIC is not an option because of the version.

Answer 1

CREATE OR REPLACE FUNCTION foo(facid bigint, dptname text[])
   RETURNS SETOF tblitem AS
$BODY$
select * from tblitem
where  facid = $1
and    dptname = ANY($2)
$BODY$
LANGUAGE sql STABLE;

• ANY should work in Postgres 8.2.
• The function can be STABLE.
  • How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?
• While you SELECT * FROM tblitem, the function should return SETOF tblitem.