2

Im attempting to update several columns in a view very similar to this post. The difference is that Im combining in some cases up to 3 tables, and for each update on the view I would like the trigger to do an INSTEAD OF UPDATE on the appropriate table.

From the Documentation I've figured out how to update columns by hand, but with some views having up to 20 or 30 columns this will get tedious. I would like to automate the process similar to the previously mentioned post.

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

CREATE TABLE poi
(
  poi_id SERIAL PRIMARY KEY,
  col_a integer DEFAULT 0,
  col_b integer DEFAULT 0,
  col_c integer DEFAULT 0
 )
/

CREATE TABLE points
(
  poi_id integer REFERENCES poi(poi_id),
  col_d varchar(50),
  col_e varchar(50),
  col_f varchar(5)
 )
/

-- Insert some example values
INSERT INTO poi (col_a, col_c) VALUES(1,1)
/
INSERT INTO points (poi_id, col_d, col_e, col_f) 
VALUES(1, 'col d description', 'col e description', 'name')
/

CREATE VIEW poi_view as 
SELECT poi.poi_id, 
poi.col_a, 
poi.col_b, 
points.col_d,
points.col_e 
FROM poi
JOIN points
ON poi.poi_id=points.poi_id
/

-- Create Instead of Trigger 
CREATE OR REPLACE FUNCTION poi_view_update()
RETURNS TRIGGER as 
$$
BEGIN
  IF (OLD.col_a != NEW.col_a) THEN
    UPDATE poi SET col_a = NEW.col_a 
    WHERE poi_id = OLD.poi_id;
    IF NOT FOUND THEN RETURN NULL; END IF;
    RETURN NEW;
  END IF;
  RAISE NOTICE 'No update performed';
  RETURN NEW;
  END;
$$
LANGUAGE plpgsql
/

CREATE TRIGGER poi_update_trigger 
INSTEAD OF UPDATE ON poi_view FOR EACH ROW EXECUTE PROCEDURE poi_view_update()
/

Query 1:

select * from poi

Results:

| poi_id | col_a | col_b | col_c |
|--------|-------|-------|-------|
|      1 |     1 |     0 |     1 |

Query 2:

select * from points

Results:

| poi_id |             col_d |             col_e | col_f |
|--------|-------------------|-------------------|-------|
|      1 | col d description | col e description |  name |

Query 3:

select * from poi_view

Results:

| poi_id | col_a | col_b |             col_d |             col_e |
|--------|-------|-------|-------------------|-------------------|
|      1 |     1 |     0 | col d description | col e description |

Query 4:

UPDATE poi_view SET col_a = 0

Results:

Query 5:

select * from poi_view

Results:

| poi_id | col_a | col_b |             col_d |             col_e |
|--------|-------|-------|-------------------|-------------------|
|      1 |     0 |     0 | col d description | col e description |

UPDATE 3/7/18

In attempting to answer my own question, Ive decided to approach it as plainly as possible, by creating the appropriate variables, building a query, then simply using the EXECUTE command. So far I have

DECLARE
   v_schema  text := quote_ident(TG_TABLE_SCHEMA);
   v_name text := quote_ident(TG_TABLE_NAME);
   column_name text;
   t_schema text := 'my_schema';
   t_name information_schema.tables.table_name%TYPE;
 BEGIN
    column_name = ???
    t_name = SELECT DISTINCT table_name 
    FROM information_schema.view_column_usage 
    WHERE view_schema = v_schema 
    AND table_schema = t_schema 
    AND column_name = column_name;

My current issue is how do I find the column name from the original table the column came from?

1

I was able to solve my problem in a more manual way. It took some time to complete for all columns but at least it is done. Heres an example, of what I did.

-- update col_d
        IF (NEW.col_d != OLD.col_d) THEN
          UPDATE poi
          SET col_d = NEW.col_d
          WHERE poi_id = NEW.poi_id;
          RAISE NOTICE 'col_d was % , changed to %',OLD.col_d,NEW.col_d;
        END IF;
-- in case col_d is NULL
        IF OLD.col_d IS NULL AND NEW.col_d IS NOT NULL THEN
          UPDATE poi
          SET col_d = NEW.col_d
          WHERE poi_id = NEW.poi_id;
          RAISE NOTICE 'col_d updated NULL to %', NEW.col_d;
        END IF;

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.