Thread: user aggregate with exception handling

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?

j. z. wrote
> 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
>
> How can I achieve the goal?

You need to create a custom aggregate function.

http://www.postgresql.org/docs/9.2/interactive/sql-createaggregate.html

Which involves creating two normal functions ( though you can possibly
leverage an existing final func ).

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/user-aggregate-with-exception-handling-tp5811279p5811289.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.