user aggregate with exception handling - Mailing list pgsql-novice

From j. z.
Subject user aggregate with exception handling
Date
Msg-id izqvviqaijhwxhojxgyg@grws
Whole thread Raw
Responses Re: user aggregate with exception handling  (David G Johnston <david.g.johnston@gmail.com>)
List pgsql-novice
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.

How can I achieve the goal?

pgsql-novice by date:

Previous
From: Sergey Konoplev
Date:
Subject: Re: How to practice on EXPLAIN/optimizing queries/constructing indices
Next
From: David G Johnston
Date:
Subject: Re: user aggregate with exception handling