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

Unless your view satisfies all 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.

Answer 3

Another approach is to create a fake join. Any joins immediately disqualify it from being an updatable view. For example:

CREATE VIEW public.west_country as select
city.id,
city_name,
country_id
from public.city
where city.country_id = 1 
WITH CHECK OPTION;

This would create an updatable view. But if you use the code snipped below with the join statement it will be read only.

CREATE VIEW public.west_country_rw as select
city.id,
city_name,
country_id
from public.city
left join (select -1 as "id" ) as tmp on tmp.id = city.id
where city.country_id = 1;