Looping through table to get single intersection from N>2 geometries using PostGIS?

This is a great application for a user-defined aggregate function, and I'm a bit wondering why this particular aggregate doesn't already exist in PostGIS. At its core, an aggregate function needs to do nothing more than iterate over a set of rows, maintaining a state (of type stype), and repeatedly calling a function (sfunc) that transforms a state and a row into a new state.

If this sounds like the reduce operator in languages like JavaScript, you're right - it's the exact same thing.

Aggregates can be complex to define (see docs), and it doesn't help that there are multiple versions of the syntax. But this is a pretty simple case:

CREATE AGGREGATE full_intersection (
  basetype = geometry,
  stype = geometry,
  sfunc = ST_Intersection
);

Here's an example of this aggregate in action, producing the inside of a Venn diagram:

CREATE table test (geom geometry);
INSERT INTO test SELECT ST_Buffer(ST_MakePoint(0,0), 0.75);
INSERT INTO test SELECT ST_Buffer(ST_MakePoint(1,0), 0.75);
INSERT INTO test SELECT ST_Buffer(ST_MakePoint(0.5,1), 0.75);

SELECT full_intersection(geom) FROM test;

You can do the following using a loop by converting your geom to an array:

create or replace function ST_IntersectionArray(geom geometry[]) returns geometry as $$
declare
   i integer;
   tmpGeom geometry;
begin
    tmpGeom := geom[1];
    FOR i IN 1..array_length(geom,1) LOOP
      tmpGeom:= ST_Intersection(tmpGeom,geom[i]);
    END LOOP;
    return tmpGeom;
end;
$$
LANGUAGE plpgsql;

And the result (table=your table name)

SELECT ST_IntersectionArray(ARRAY(select geom from table));

to check! insert your result in a new table (result)

Create table result as 
SELECT ST_IntersectionArray(ARRAY(select geom from table)); 

  select * from table result

Tested (the result in QGIS) working.

Reference (ST_Intersection of multiple polygons in one table)

PS: I tried the answer using aggregate, it gives me the same with my answer, actually perfect to use aggregate. but sometimes using arrays has an advantage if someone wants to work on a just specific number of polygons