CODEWITHSUNDEEP
postgresqlpostgis
ChrisInCambo
ChrisInCambo

Reputation: 8755

Handling Exception From C Function

I have a function that is calling a function from a C library. Is there anyway that I can catch that exception from the Postgres function that's calling the C function?

Here's the function that I'm calling:

-- Function: public.st_makevalid(geometry)

-- DROP FUNCTION public.st_makevalid(geometry);

CREATE OR REPLACE FUNCTION public.st_makevalid(geometry)
 RETURNS geometry AS
'$libdir/postgis-2.0', 'ST_MakeValid'
  LANGUAGE c IMMUTABLE STRICT
  COST 100;
ALTER FUNCTION public.st_makevalid(geometry) OWNER TO postgres;
COMMENT ON FUNCTION public.st_makevalid(geometry) IS 'args: input - Attempts to make an         invalid geometry valid w/out loosing vertices.';

Here is the function that I'm calling it from:

CREATE OR REPLACE FUNCTION public.mango_repair(geometry)
  RETURNS geometry AS
$BODY$
    DECLARE
        the_geom geometry := $1;
        reason text := ST_IsValidReason(the_geom);
    BEGIN
        IF reason LIKE 'Self-intersection%' THEN
            the_geom = ST_MakeValid(ST_Boundary(the_geom));
        END IF;
        RETURN the_geom;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'something went wrong';
            RETURN the_geom;
    END;
$BODY$
  LANGUAGE plpgsql STABLE STRICT
  COST 100;
ALTER FUNCTION public.mango_repair(geometry) OWNER TO postgres;

And here's how I'm calling it from SQL:

UPDATE "test_layer" SET the_geom = mango_repair(the_geom) WHERE NOT ST_IsValid(the_geom);

When I run that SQL I get the following error and the mango_repair function doesn't enter the EXCEPTION block as expected.

Here is the error message that I'm receiving:

ERROR:  Geometry type (MultiLineString) does not match column type (MultiPolygon)


********** Error **********

ERROR: Geometry type (MultiLineString) does not match column type (MultiPolygon)
SQL state: 22023

Upvotes: 1

Views: 462

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324445

If I'm guessing what you mean correctly, then I suspect you have a PostgreSQL function implemented in C that is raising a PostgreSQL exception. If so, you can't catch and handle that in normal SQL - but you can use a PL/PgSQL BEGIN ... EXCEPTION block. See the PL/PgSQL documentation for details on its exception handling features.

Further discussion revealed that the real problem is nothing to do with exception handling, and is in fact a mismatch between geometry types in the UPDATE query the function is being called from.

Upvotes: 1

Related Questions