I am looking for a function that will return the intersection of 2 or more polygons (geography type).
I am aware of ST_UNION, ST_COLLECT but it works only for geometry type.
Any tip will be really appreciated
You can cast to geometry and do the calculation. If your data goes over the poles or date-line that could be problematic. Also if you have long edges, in which the great-circle interpolation line differs from a simple cartesian interpolation. In those cases you might have to create your own aggregate function that re-projects to something reasonable before running the operation.
According to bostongis, there is no UNION that works on geography type. So you have to first cast geography to geometry:
Then apply ST_UNION and cast back.