Update a view based on CASE WHEN

Question

I want to change the value on each column on a table. This is what I got:

CREATE VIEW old_employee AS
  SELECT *
    FROM dblink('dbname=mydb', 'select study, gloves, apron, vocation from personnel')
    AS t1(study char(10), gloves integer, apron integer, vocation integer);

SELECT * FROM old_employee;

   study    |  gloves | apron  | vocation 
------------+------------------+------------+
 STUDY1     |     1   |   1    |     1      |
 STUDY2     |     2   |   2    |     2      |
 STUDY3     |     0   |   2    |     3      |
 STUDY3     |     1   |   0    |     4      |

My desired output

   study    |  gloves | apron  | vocation 
------------+------------------+------------+
      1     |     51   |   54  |     35      |
      2     |     53   |   56  |     37      |
      3     |     52   |   56  |     43      |
      4     |     51   |   55  |     45      |

My first thought is to use multiple CASE WHEN on an update. Something like:

UPDATE old_employee
   SET  study = (
                 SELECT studies.id
                 FROM studies
                 INNER JOIN old_employee
                 ON studies.name_string = old_employee.study
                ),
      gloves = (
              SELECT CASE 
                WHEN gloves = 0 THEN 52
                WHEN gloves = 1 THEN 51
                WHEN gloves = 2 THEN 53
                     END
               FROM old_employee
             ),
        apron = (
                SELECT CASE 
                    WHEN apron = 0 THEN 55
                    WHEN apron = 1 THEN 54
                    WHEN apron = 2 THEN 56
                END
                FROM old_employee
               ),
      vocation = (
                SELECT CASE 
                    WHEN vocation = 1 THEN 35
                    WHEN vocation = 2 THEN 37
                    WHEN vocation = 3 THEN 43
                    WHEN vocation = 4 THEN 45           
                END
                FROM old_employee
                );

Obviously this is not working and I'm wondering if i'm approaching this problem correctly or missing something. I'm a programmer and new to SQL so im not quite used to the SQL-mindset.

Is updating a view that gets data through DBLink supported at all? — , Oct 22 '15 at 09:48
I have no idea..I've read some post that you can't update a view and another post that in a newer version of postgresql you can update? — Michael Bui, Oct 22 '15 at 09:53
You _can_ update views (simple views without any additional code) - but I'm pretty sure you can't update a view that gets the data from remote server — , Oct 22 '15 at 10:10

Pontus Ullgren · Accepted Answer · 2015-10-22T11:43:39.100

While views are updatable in postgesql 9.3 I would consider it bad practice, especially when working with remote databases, since it would change the original table. So I would say that it is better to update the underlying table instead if you are not really sure of what you are doing.

Also if you run an earlier version of postgresql views are not updatable.

Instead of updating the view you can add the data translation into the view definition.

CREATE VIEW old_employee AS
  SELECT studies.id, 
         (SELECT CASE 
             WHEN t1.gloves = 0 THEN 52
             WHEN t1.gloves = 1 THEN 51
             WHEN t1.gloves = 2 THEN 53
         END) as gloves, 
         (SELECT CASE 
                    WHEN t1.apron = 0 THEN 55
                    WHEN t1.apron = 1 THEN 54
                    WHEN t1.apron = 2 THEN 56
          END) as apron,
          (
        SELECT CASE 
            WHEN t1.vocation = 1 THEN 35
            WHEN t1.vocation = 2 THEN 37
            WHEN t1.vocation = 3 THEN 43
            WHEN t1.vocation = 4 THEN 45           
        END) as vocation
    FROM studies INNER JOIN dblink('dbname=mydb', 'select study, gloves, apron, vocation from personnel')
    AS t1(study char(10), gloves integer, apron integer, vocation integer)
    ON studies.name_string = t1.study;

The post you are referring to is a bit outdated. Simple views are updateable without any further action since 9.3 http://www.postgresql.org/docs/current/static/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS — , Oct 22 '15 at 11:25
Thanks for pointing that out, I will update the answer. Still I believe that running the update on the view in this case is not a good idea. — Pontus Ullgren, Oct 22 '15 at 11:39
You are right, and in _this case_ it is actually impossible because the view gets data from a remote server — , Oct 22 '15 at 11:40
Thank you very much humble sir. This worked out perfectly for me, thank you for existing! — Michael Bui, Oct 22 '15 at 11:41

Update a view based on CASE WHEN

1 Answers1