Postgres View CREATE and ALTER produces errors

[expired user #9074]'s profile image [expired user #9074] posted 8 years ago in General Permalink
Hello,

I was trying to change a view's definition, then tried to create a new view and was receiving errors. I've noted the problems below and what I believe to be the solutions. The documentation I'm linking to is Postgres 9.4, but I'm using version 9.2 and can confirm that they work.

When creating an example view, the dialog prompts for the ALGORITHM and UPDATES options, which produces this output, as an example:
CREATE ALGORITHM = UNDEFINED VIEW "test_view" AS SELECT trailers.id FROM trailers WITH CASCADED CHECK OPTION;
This produces an error with the ALGORITHM statement. According to the below documentation, the Postgres syntax should work as follows.
http://www.postgresql.org/docs/9.4/static/sql-createview.html

Instead of --> Should be
CREATE ALGORITHM = UNDEFINED VIEW view_name --> CREATE VIEW view_name (I think?)
CREATE ALGORITHM = MERGE VIEW view_name --> CREATE VIEW view_name (I think?)
CREATE ALGORITHM = TEMPTABLE VIEW view_name --> CREATE TEMPORARY VIEW view_name

Then for changing a query definition:
ALTER VIEW view_name AS query --> CREATE OR REPLACE VIEW view_name AS query

and if you do this maybe you also need to re-set the owner? not sure about this, but it's shown in the SQL pane in pgAdmin
ALTER TABLE view_name OWNER TO current_user;
ansgar's profile image ansgar posted 8 years ago Permalink
Thanks a lot for the hints. Will try to implement PostgreSQL specific queries here.

Please login to leave a reply, or register at first.