On new geom insert - PostGIS error while adding features: ERROR: more than one row returned by a subquery used as an expression

[SPlanzer]

The error PostGIS error while adding features: ERROR: more than one row returned by a subquery used as an expression is on insert of new geom.

This is via the newly edited rules that now return (see below)

When creating the rule the where clause is not as expected. WHERE g.feat_id = feat_id > WHERE g.feat_id = g.feat_id Note: the addition of the g. will result in multiple rows being returned.

Below code to create the rule

CREATE OR REPLACE RULE feature_point_ins AS ON INSERT TO feature_point
DO INSTEAD
    INSERT INTO gazetteer.feature_geometry(
        feat_id,
        geom_type,
        shape
        )
    VALUES (
        NEW.feat_id,
        CASE
            WHEN GeometryType( NEW.SHAPE) like '%POINT' THEN 'X'
            WHEN GeometryType( NEW.SHAPE) like '%LINESTRING' THEN 'L'
            WHEN GeometryType( NEW.SHAPE) like '%POLYGON' THEN 'P'
        END,
        NEW.shape
        )
    RETURNING
        (SELECT g.geom_id FROM feature_geometry g WHERE g.feat_id = feat_id),
        feat_id,
        gaz_preferredName(feat_id) as name,
        shape;

The rule definition once installed in the DB

CREATE OR REPLACE RULE feature_point_ins AS
    ON INSERT TO gazetteer.feature_point DO INSTEAD  INSERT INTO gazetteer.feature_geometry (feat_id, geom_type, shape) 
  VALUES (new.feat_id, 
        CASE
            WHEN geometrytype(new.shape) ~~ '%POINT'::text THEN 'X'::text
            WHEN geometrytype(new.shape) ~~ '%LINESTRING'::text THEN 'L'::text
            WHEN geometrytype(new.shape) ~~ '%POLYGON'::text THEN 'P'::text
            ELSE NULL::text
        END, new.shape)
  RETURNING ( SELECT g.geom_id
           FROM gazetteer.feature_geometry g
          WHERE g.feat_id = g.feat_id), feature_geometry.feat_id, gazetteer.gaz_preferredname(feature_geometry.feat_id) AS name, feature_geometry.shape;
``

[strk]

WHERE g.feat_id = g.feat_id is definitely wrong, as it will always be true. I don't know enough of the query to know if we can use NEW.feat_id or something like that. All those subselects surely look dangerous (and expensive, performance-wise). Having tests in place would greatly help :)

[SPlanzer]

That's the problem we can not use NEW.feat_id

WHERE g.feat_id = g.feat_id was a mistake and PR #166 is to resolve this

I understand the sub queries are expensive - It is however a workflow that is not used heavily.