user aggregate with exception handling

Поиск
Список
Период
Сортировка
От j. z.
Тема user aggregate with exception handling
Дата
Msg-id izqvviqaijhwxhojxgyg@grws
обсуждение исходный текст
Ответы Re: user aggregate with exception handling  (David G Johnston <david.g.johnston@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Sergey Konoplev
Дата:
Сообщение: Re: How to practice on EXPLAIN/optimizing queries/constructing indices
Следующее
От: David G Johnston
Дата:
Сообщение: Re: user aggregate with exception handling