update tables data dynamically

Question

Can u help me in creating a trigger and function so that every time i change the flag in table A the buffer distance should get changed and also its geometry field also , ie the polygon area?

I have two tables named A and B.

Table A has the fields GID (primary key), FLAG, Buffer_distance, and the geometry field. Table B has the fields GID and buffer_distance, and has only three records as shown below:

GID(PK) Buffer_distance(Double Precision)
1 1000
2 2000
3 3000

Table A's structure:

gid(PK) buffer_distance(Double Precision) flag(Text) the_geom( Geometry)
1 200 1 the_geom
2 100 2 the_geom
3 100 3 the_geom
4 500 3 the_geom
5 300 2 the_geom
6 899 1 the_geom

What I want is the value of flag in table A to be updated from table B, and as soon as I updated the value of flag in table A the buffer_distance in A should also get replaced along with the geometry.

If what you want is to update buffer_distance in Table A based on the flag value (ie, buffer_distance=2000 when flag=2), can anyone write me an SQL case statement that will accomplish this. along with this the_geom Filed the geometry should also change with the new buffer_distance. i used the following code. help me to correct the same

create or replace function update_point_buffer() returns trigger as
$$
 begin
 -- delete
IF (TG_OP = 'UPDATE') THEN
UPDATE tablea
 SET buffer_distance = (SELECT tableb.buffer_distance
 FROM tableb
 WHERE 
 "tableb.gid" = "tablea.flag");
 END IF;
 return NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_upgradeBufferTrigger
 AFTER UPDATE ON tablea
 FOR EACH ROW
 EXECUTE PROCEDURE update_point_buffer();

---

i have used this query bit it has given this error ERROR: syntax error at or near "v_output" LINE 2: v_output integer;

create or replace function update_point_buffer(v_pointflag) returns number as---------------------Return buffer distance v_output number ; begin

SELECT buffer_distance into v_output FROM point_buffer WHERE gid = v_pointflag;

return v_output; END;

CREATE TRIGGER trg_upgradeBufferTrigger-----------------------------------Update table from B to A AFTER UPDATE ON point FOR EACH ROW

begin

update point_buffer set buffer_distance= (select buffer_distance from table B where gis= :new.gis)

where gis= :new.gis

end;

can any body correct it for use.

• blah238: possible duplicate of gis related question
• user12766: UPDATE tableA SET buffer_distance = CASE WHEN flag = 1 THEN 1000 WHEN flag = 2 THEN 2000 WHEN flag = 3 THEN 3000 END; sir can u help me in creating a trigger and function so that every time i change the flag in table A the buffer distance should get changed and also its geometry field also , ie the polygon area .
• underdark: Please edit your question instead of posting new information in the answers section. Also, you seem to have created a second account. Do you want me to merge them for you? Which one do you want to continue using?

Answer 1

Something like this will update the buffers to the new values in Table A:

UPDATE tableA
SET buffer_distance =
 CASE
 WHEN flag = 1 THEN 1000
 WHEN flag = 2 THEN 2000
 WHEN flag = 3 THEN 3000
 END;