How to pass named parameters to ST_Makevalid method in SAP Hana trigger?

[hannu_huttunen43]

It is easy and simple to use St_Makevalid method of st_geometry in SQL-statements like documented in https://help.sap.com/docs/SAP_HANA_PLATFORM/cbbbfc20871e4559abfd45a78ad58c02/207f2c2aca5b46c1b064f22...

Like this:

SELECT ST_GeomFromText('LINESTRING(0 0, 0 0)').ST_MakeValid(degenerate => 'COLLAPSE', polygonrule => 'NONZERO').ST_AsText() FROM DUMMY;

But what is the syntax when using the method with named parameters in function, procedure or trigger?

Here is the example:

CREATE TABLE geom_table (id bigint, geom st_geometry);

Simple validation trigger for the geometry:

CREATE OR replace TRIGGER ins_upd_geom_table BEFORE INSERT OR UPDATE OF geom ON geom_table REFERENCING OLD ROW OLD, NEW ROW NEW
FOR EACH ROW
BEGIN

DECLARE tmp_geom st_geometry;

tmp_geom := :NEW.geom;
NEW.geom := :tmp_geom.ST_MakeValid(degenerate => 'COLLAPSE', polygonrule => 'NONZERO');

END;

Result is:

SQL Error [257] [HY000]: SAP DBTech JDBC: [257] (at 260): sql syntax error: incorrect syntax near "=>": line 7 col 48 (at pos 260)
Error position: line: 16 pos: 259

What is the correct syntax for named parameters?

[mkemeter · Accepted solution]

ST_MakeValid has just been released and it seems you've hit a bug here. Generally your approach should be working. However there is an issue with using ST_MakeValid in this constellation. For the time being, you can workaround this by storing and processing the geometries within a table variable instead of a variable.

I know, it doesn't like nice, but until the issue is properly handled, something like this should (functionally) do the job:

CREATE TABLE GEOM_TABLE (ID BIGINT, GEOM ST_Geometry(3857));

CREATE OR REPLACE TRIGGER INS_UPD_GEOM_TABLE
BEFORE INSERT OR UPDATE OF GEOM ON GEOM_TABLE
REFERENCING NEW ROW new_row FOR EACH ROW
BEGIN
    DECLARE geom ST_Geometry;
    DECLARE dml_tab TABLE (geom ST_Geometry(3857));
    DECLARE no_dml_tab TABLE (geom ST_Geometry(3857));
    geom := :new_row.geom;
    IF :geom.ST_IsValid() = 0 THEN
        INSERT INTO :dml_tab VALUES (:geom);
        UPDATE :dml_tab SET geom = geom.ST_MakeValid(degenerate => 'COLLAPSE', polygonrule => 'NONZERO');
        no_dml_tab = SELECT geom FROM :dml_tab;
        new_row.geom = :no_dml_tab.geom[1];
    END IF; 
END;

INSERT INTO GEOM_TABLE VALUES (1, ST_GeomFromText('POLYGON((0 0, 1 0, 0 1, 1 1, 0 0))', 3857));
SELECT * FROM GEOM_TABLE;

[hannu_huttunen43]

Thanks Mathias,

I'll try this solution

BR,

Hannu