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;