CODEWITHSUNDEEP
postgresql
Jacobian
Jacobian

Reputation: 10832

ERROR: missing FROM-clause entry for table "new"

I have a parent table layer_1_ and a number of child tables layer_1_points, layer_1_linestrings etc. which contain some geometry data. Each child table has its own geometry constraint. So, for example, layer_1_points has this constraint:

CONSTRAINT enforce_geotype_geom_geom CHECK (geometrytype(geom) = 'POINT'::text)

Whereas layer_1_linestrings table has this constraint:

CONSTRAINT enforce_geotype_geom_geom CHECK (geometrytype(geom) = 'LINESTRING'::text)

Many other layer tables have similar names: layer_2_, layer_3_, ..., layer_N_. And all of them have their own child tables. What I want to achive is that when a user inserts to a parent table (layer_N_), then this insert statement should be forwarded to a particular child table (layer_N_points etc.). So, for example, when I do:

INSERT INTO layer_1_ (geom) VALUES(ST_GeomFromText('POINT(0 0)', 3857))

I should actually insert to layer_1_points, because geom type is POINT. To achive all this I created this trigger function and the trigger itself:

CREATE OR REPLACE FUNCTION trigger_layer_insert() 
  RETURNS trigger AS 
$$ 
DECLARE 
    var_geomtype text;
    table_name text;
    layer_id text := (TG_ARGV[0])::text;
BEGIN
    var_geomtype := geometrytype(NEW.geom);    
    IF var_geomtype = 'POINT' THEN
        table_name := (SELECT concat ('layer_', layer_id, '_points'));
    ELSIF var_geomtype = 'MULTIPOINT' THEN
        table_name := (SELECT concat ('layer_', layer_id, '_multipoints'));       
    ELSIF var_geomtype = 'LINESTRING' THEN
        table_name := (SELECT concat ('layer_', layer_id, '_linestrings'));
    ELSIF var_geomtype = 'MULTILINESTRING' THEN
        table_name := (SELECT concat ('layer_', layer_id, '_multilinestrings'));
    ELSIF var_geomtype = 'POLYGON' THEN
        table_name := (SELECT concat ('layer_', layer_id, '_polygons'));
    ELSIF var_geomtype = 'MULTIPOLYGON' THEN
        table_name := (SELECT concat ('layer_', layer_id, '_multipolygons'));
    END IF;

    EXECUTE '
        INSERT INTO ' || table_name || ' 
        SELECT * FROM (SELECT NEW.*) AS t
    ';

    RETURN NULL;   
END; 
$$ 
LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER trigger_layer_1_ BEFORE INSERT
ON layer_1_ FOR EACH ROW 
EXECUTE PROCEDURE trigger_layer_insert(1);

However, when I do actual insert like:

INSERT INTO layer_1_ (geom) VALUES(ST_GeomFromText('POINT(0 0)', 3857))

I get an error message:

ERROR: missing FROM-clause entry for table "new"
LINE 3:         SELECT * FROM (SELECT NEW.*) AS t
                                  ^
QUERY:  
    INSERT INTO layer_1_points 
    SELECT * FROM (SELECT NEW.*) AS t

So, what is wrong with SELECT NEW.* and how can I fix it? Thanks!

EDIT

I also tried this:

EXECUTE '
    INSERT INTO ' || table_name || ' 
    SELECT * FROM (SELECT NEW.*) AS t
' USING NEW;

But it has no effect.

Upvotes: 6

Views: 9258

Answers (1)

Abelisto
Abelisto

Reputation: 15624

When you execute something using PLPGSQL statement EXECUTE it runs in the different context so local variables is not visible there. To pass variable(s) the EXECUTE '<SQL script>' USING <variables list>; form is used:

EXECUTE 'insert into table(field1, field2) values ($1, $2)' USING var1, var2;

So the statement should be:

EXECUTE 'INSERT INTO ' || table_name || ' SELECT * FROM SELECT $1.*) AS t'
      USING NEW;

But much more secure is using format function:

execute format('INSERT INTO %I SELECT * FROM SELECT $1.*) AS t', table_name)

Upvotes: 13

Related Questions