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