Creating a view in PostGIS - Having issue with view keeping my query

Question

I'm trying to make a view from a table without a geometry column. My first issue is when I click on "New View" and then put the query in and click 'OK'. The query statement is replaced with some other statement for the view. It doesn't give me an error. Is there a permission issue when you create a view? I'm using pgAdmin III. Database version 9.6.

Update. I saw that everything stopped at the ";" in the query. I removed the ; in the query and now it's giving me a syntax error.

Here is the query that I want in the view.

SELECT j.speed, j.level, j.length, j.delay, j.city, j.street, j.line, 
j.pub_utc_date at time zone 'utc' at time zone 'america/denver',j.id
from waze.jams j
ORDER BY j.pub_utc_date DESC
WITH
 lines AS( 
 SELECT id,
       ST_MakeLine(
         ST_MakePoint(
           (line -> n ->> 'x')::NUMERIC,
           (line -> n ->> 'y')::NUMERIC
         )
       ) AS geom
FROM waze.wazejams
CROSS JOIN generate_series(0, jsonb_array_length(line)) AS n
GROUP BY id)


UPDATE waze.wazejams AS a
SET geom_line = ST_SetSRID(b.geom, 4326)
FROM lines AS b
WHERE a.id = b.id

Answer 1

You can't put an UPDATE statement in a VIEW. Views are for queries (SELECT statement).

Answer 2

It looks kinda messy... You have a SELECT then a WITH ... AS SELECT plus and UPDATE statement in the mix. It won't process.

j.pub_utc_date at time zone 'utc' at time zone 'america/denver',j.id

And did you mean AS instead of at? You have time_zone column declared twice, no commas between columns.

https://www.postgresql.org/docs/9.6/static/sql-createview.html

Maybe you can create a view with just the lines and join them with the waze.jams, but I am not sure what is it you're trying to achieve exactly.

By the way a view is automatically updatabe if certain conditions are met (see link above), so if waze.wazejams is a table or a simple view your UPDATE statement should look like this:

UPDATE waze.wazejams AS a
SET geom_line = (SELECT ST_SetSRID(b.geom, 4326)
FROM lines AS b
WHERE a.id = b.id);