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.

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;