editing a view

[Jago6060]

Hi all!

 

I have a view in a postgresql database that I need to change.  It only has 2 columns, col1 being an autogenerated INT, col2 being a string.  The values in col2 are basically just labels that need to be changed to something else.  How do I change the values in col2?

[artacus]

As long as you are not changing the data types of the columns, just make sure it says 'CREATE OR REPLACE VIEW' and rerun the create script.

[Jago6060]

As long as you are not changing the data types of the columns, just make sure it says 'CREATE OR REPLACE VIEW' and rerun the create script.

 

I don't have a script to create the values in the view, I have to do it manually.  Can I do something like...

 

ALTER VIEW view1 REPLACE label1 WITH newLabel WHERE label_id=1;(I'm not well versed in MySQL btw :-P)

[artacus]

The easiest way to get the source is to just click on the view name in PgAdmin 3. It will load the definition in the SQL pane, and you can also see it in the definition field in the properties tab.

 

The alter view statement is kinda weird in Postgres. The correct syntax is:

 

ALTER TABLE view1 RENAME COLUMN label1 TO newLabel;

 

Yes, that says 'table'... don't ask.

[Jago6060]

The easiest way to get the source is to just click on the view name in PgAdmin 3. It will load the definition in the SQL pane, and you can also see it in the definition field in the properties tab.

 

The alter view statement is kinda weird in Postgres. The correct syntax is:

 

ALTER TABLE view1 RENAME COLUMN label1 TO newLabel;

 

Yes, that says 'table'... don't ask.

 

That renames the column...is there a way to rename a field entry?

Ex: change label1 where id=1;  ???

[artacus]

Oh, are you talking about the values?

 

SELECT id, CASE WHEN id = 1 THEN 'foo' 
WHEN id BETWEEN 12 AND 14 THEN 'bar'
ELSE label END AS label
FROM view1

[Jago6060]

Oh, are you talking about the values?

 

SELECT id, CASE WHEN id = 1 THEN 'foo' 
WHEN id BETWEEN 12 AND 14 THEN 'bar'
ELSE label END AS label
FROM view1

 

Yeah I need to edit some of the values.  As I said, I'm not well versed in MySQL so I'm not really sure how to implement the query your provided.  Here is the view in question...

 

View:im_project_types
project_type_id |      project_type
-----------------+-------------------------
              85 | Unknown
              86 | Other
            2501 | Consulting Project
              97 | Strategic Consulting
              98 | Software Maintenance
              99 | Software Development
            4300 | Bug Tracker Container
            4305 | Bug Tracker Task
        10000012 | Directory
              92 | Technology
            2500 | Translation Project
              90 | Linguistic Validation
              91 | Localization
              93 | Trans Only
              95 | Proof Only
              94 | Trans + Int. Spotcheck
            2503 | Trans Only (Dynamic WF)
              89 | Trans + Edit + Proof
              88 | Edit Only
              87 | Trans + Edit
              96 | Glossary Compilation
             100 | Task
        10000006 | Implementation
        10000007 | Optimization
        10000008 | Monthly Bill Review
        10000009 | TestCategory
        10000010 | Administration
        10000011 | Monthly Review
(28 rows)

Lets say I want to change the project_type where project_type_id=96.  Could you give me an example of how I would use the actual view data to replace the foo,bar, and label variables in your above statement?

[artacus]

Alright, lets not confuse MySQL and Postgres here.

 

I can't give you much more without spoon feeding you. But what you need is the case statement.

 

http://www.postgresql.org/docs/current/static/functions-conditional.html

[Jago6060]

Alright, lets not confuse MySQL and Postgres here.

 

I can't give you much more without spoon feeding you. But what you need is the case statement.

 

http://www.postgresql.org/docs/current/static/functions-conditional.html

 

Ok I'll give this a shot...Just let me know if I'm correct

 

SELECT project_status_id, CASE WHEN id = 96 THEN 'ThisIsMyLabel'
ELSE originalLabel END AS originalLabel
FROM im_project_types;

 

Am I close?  I interpret this statement as "select project_status_id, when the id is 96, change the label to ThisIsMyLabel, else leave the original label; do this is the im_project_types view".  Am I correct in my translation?

[artacus]

Am I close?

 

You got it. Oh and just comment out the first line to test when you are making views.

 

--CREATE OR REPLACE VIEW im_project_types AS
SELECT project_status_id, CASE WHEN id = 96 THEN 'ThisIsMyLabel'
ELSE originalLabel END AS originalLabel
FROM im_project_types;