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.

If this is for SQL Server Reporting Services, then why are you using Postgres? Or what's SSRS supposed to mean? And if you are using Postgres, why the [long dead and forgotten version 8.2](http://www.postgresql.org/support/versioning/)? — Erwin Brandstetter, May 18 '15 at 22:19
company politics I guess. But that is the environment I have to work with — katie, May 19 '15 at 13:37
they had a SQL server backend but they are migrating everything to greenplum. — katie, May 19 '15 at 16:39
That's the kind of information that should be in the question. Especially since Greenplum is *not* Postgres. It's just based on Postgres. — Erwin Brandstetter, May 19 '15 at 16:43

score 0 · Accepted Answer · edited May 23 '17 at 12:21

0

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.

edited May 23 '17 at 12:21

Community

1
1

answered May 19 '15 at 13:48

Erwin Brandstetter

605,456
145
1,078
1,228

I could have sworn I tried ANY yesterday.. ANYway, it worked! Thanks for saving me a rewrite! and some good info on stable – katie May 19 '15 at 16:39

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

1 Answers1