How to create a read only view in postgresql , similar to oracle?

Question

I want to create read only view in PostgreSQL. We can create in Oracle, but unable to do in PostgreSQL.

I tried to create a read only view but I get a syntax error at READ ONLY.

CREATE OR REPLACE VIEW VIEW NAME() 
from table names 
where filter condition1=filter 
  condition2 
with READ ONLY; 

But the READ ONLY doesn't work in PostgreSQL. How do I create a read only view in PostgreSQL?

Answer 1

I don't think that Postgres provides a way to explicitly define a view as read-onöy.

The documentation states:

Simple views are automatically updatable

However:

A more complex view that does not satisfy all these conditions is read-only by default.

The documentation lists the limitations of read-only views:

• A view is automatically updatable if it satisfies all of the following conditions:

• The view must have exactly one entry in its FROM list, which must be a table or another updatable view.

• The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.

• The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.

• The view's select list must not contain any aggregates, window functions or set-returning functions.

• Simple views are automatically updatable: the system will allow INSERT, UPDATE and DELETE statements to be used on the view in the same way as on a regular table. A view is automatically updatable if it satisfies all of the following conditions:

• The view must have exactly one entry in its FROM list, which must be a table or another updatable view.

• The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.

• The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.

• The view's select list must not contain any aggregates, window functions or set-returning functions

So if your view dodes not satisfies all these conditions, it is read-only. If you have a simple view that you want to make read-only, a (suboptimal) option would be to tweak its definition so it violates one of the above rules.

For example, you can add a dummy WITH clause:

CREATE VIEW myview AS 
WITH dummy AS (SELECT 1)
-- real view definition here

Answer 2

You should handle this with permissions in PostgreSQL.

Just make sure that nobody has permissions to modify the view.

Note that you can also revoke privileges from the view owner.