Search code examples
arrayspostgresqlmultivaluegreenplumpostgresql-8.2

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

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.

Solution 
  • 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;