Postgresql Trigger to insert records in my table from another one

Question

I spent few days trying to resolve this question but it was impossible.

I have two tables:

Table1.

CREATE TABLE point
(
 id_point integer NOT NULL DEFAULT,
 layer text,
 elevation numeric,
 geom geometry(POINT,25830)
)

Table2.

CREATE TABLE point2
(
id_point2 integer NOT NULL DEFAULT,
layer text,
elevation numeric,
geom geometry (POINTZ, 25830)
)

In this situation I want get the values from point.elevation,point.geom and insert to point2.elevation, point2.geom each time I insert a new row in point table.

Also to say I need transform the point.geom value before to insert into point2.geom like this:

geom=st_setsrid((st_makepoint(st_x(point.geom),st_y(point.geom),point.elevation)), 25830)

I tried to create this trigger but I did not get the expected result, my trigger inserted all my records from point to point2, but I need do it for each new record from point table:

The function:

CREATE OR REPLACE FUNCTION function_prueba() RETURNS TRIGGER AS $$
DECLARE
BEGIN 
 INSERT INTO 
 point2(elevation,geom)
 VALUES
 NEW.point.elevation, 
 st_setsrid
 (
 (
 st_makepoint(st_x(NEW.point.geom),st_y(NEW.point.geom),NEW.point.elevation)
 ), 25830
 )
 FROM
 point
 ; 
 RETURN new;
END;
$$ language plpgsql;

The trigger:

CREATE TRIGGER trig_geotrans
 AFTER INSERT ON point
 FOR EACH ROW
 EXECUTE PROCEDURE function_prueba();

How can I resolve this question? Thanks.

Answer 1

I don't think you need the FROM Point portion of your statement. Point is a table, so I am guessing that you are inserting the NEW value into the point2 table for every row in the point table.

I would try:

CREATE OR REPLACE FUNCTION function_prueba() RETURNS TRIGGER AS $$
DECLARE
BEGIN 
 INSERT INTO 
 point2 ( elevation, geom )
 VALUES
 NEW.point.elevation, 
 st_setsrid(
 st_makepoint(
 st_x(NEW.point.geom),
 st_y(NEW.point.geom),
 NEW.point.elevation
 ), 
 25830);
 RETURN new;
END;