1

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.

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.