Hi. I need to apply a postgis aggregate function. Since usual usage throws errors, in case of error I need to use the aggregate function with another argument to avoid an error. As far as I know I can do it as an exception handler in user defined function, but in 'create function' there seems to be some limitation on using aggregate functions. On the other hand in 'create aggregate' there's no room for exception handling.
Below is what I tried to do:
CREATE OR REPLACE FUNCTION jz_union_safe(g geometry) RETURNS geometry AS $$ BEGIN RETURN ST_union(g); EXCEPTION WHEN OTHERS THEN BEGIN RETURN ST_union(ST_Buffer(g, 0)); EXCEPTION WHEN OTHERS THEN BEGIN RETURN ST_union(ST_Buffer(g, 0.000000001)); EXCEPTION WHEN OTHERS THEN BEGIN RETURN ST_union(ST_Buffer(g, 0.00000001)); EXCEPTION WHEN OTHERS THEN BEGIN RETURN ST_GeomFromText('POLYGON EMPTY'); END; END; END; END; END $$ LANGUAGE 'plpgsql' STABLE STRICT;
The function get's created, but when called as:
select id, jz_union_safe(gu) from my_table group by 1
it complains:
ERROR: column "my_table.gu" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select id, jz_union_safe(gu) from my_table... ^
and even if I extend to : select id, jz_union_safe(gu) from my_table group by 1, gu it returns rows not aggregated on id level, but with id values multiplied.